Contents
This article discusses ways in which you can remove line wrapping (carriage return or line break) in Excel documents. In addition, here you will find information on how to replace it with other characters. All methods are suitable for versions of Excel 2003-2013 and 2016.
The reasons for the appearance of line breaks in a document are varied. It usually occurs when copying information from a web page, when another user provides you with a finished Excel workbook, or if you activate this feature yourself by pressing the Alt + Enter keys.
So, sometimes because of the line break it is difficult to find the phrase, and the contents of the column look sloppy. That is why you need to make sure that all the data is located on one line. These methods are easy to implement. Use the one you like best:
- Manually remove all line breaks to bring data on sheet 1 back to normal.
- Get rid of line breaks with formulas to start further complex information processing.
- Use a VBA macro.
- Get rid of line breaks with the Text Toolkit.
Please note that the original terms “Carriage return” and “Line feed” were used when working on typewriters. In addition, they denoted 2 different actions. More information about this can be found on any reference resource.
Personal computers and text-editing programs were developed around the characteristics of a typewriter. That is why, to indicate a line break, there are 2 non-printable characters: “Carriage return” (or CR, code 13 in the ASCII table) and “Line feed” (LF, code 10 in the ASCII table). On Windows, CR+LF characters are used together, but on *NIX, only LF can be used.
Attention: Excel has both options. When importing data from .txt or .csv files, the CR+LF character combination is more likely to be used. When using the Alt + Enter combination, only line breaks (LF) will be applied. The same will happen when editing a file received from a person working on the *Nix operating system.
Remove line break manually
Advantages: this is the easiest way.
Disadvantages: no additional features.
Follow these steps:
- Select the cells in which you want to remove or replace the line break.
- Press Ctrl + H to open the function “Find and Replace”.
- In the “Find” type Ctrl + J, after which a small dot will appear in it.
- In the field “Replaced by” enter any character to replace the line break. You can enter a space so that the words in the cells do not merge. If you need to get rid of line breaks, do not enter anything in the “Replaced by”.
- Press the button “Replace All”.
Remove line breaks with Excel formulas
Advantages: it is possible to use a chain of formulas for complex data processing. For example, you can remove line breaks and get rid of extra spaces.
Also, you may need to remove the wrap to work with the data as a function argument.
Disadvantages: you need to create an additional column and perform auxiliary actions.
- Add an extra column on the right. Name it “line 1”.
- In the first cell of this column (C2), enter a formula that will remove the line break. Below are different combinations that are suitable for all cases:
- Suitable for Windows and Unix operating systems:
=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),»»),CHAR(10),»»)
- This formula will allow you to replace a line break with another character. In this case, the data will not merge into a single whole, and unnecessary spaces will not appear:
=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),»»),CHAR(10),», «)
- If you need to get rid of all non-printable characters, including line breaks, the formula will come in handy:
=CLEAN(B2)
- Duplicate the formula in other cells of the column.
- If necessary, the data from the original column can be replaced with the final result:
- Select all cells in column C and press Ctrl + C to copy the data.
- Now select cell B2 and press Shift + F10 and then V.
- Remove the extra column.
VBA macro to remove line breaks
Advantages: Once created, a macro can be reused in any workbook.
Disadvantages: It is necessary to understand VBA.
The macro does a great job of removing line breaks from all cells on the active worksheet.
Remove line break with Text Toolkit
If you use the Text Toolkit or the Ultimate Suite for Excel, you won’t have to spend time on any manipulations.
All you need to do:
- Select the cells in which you want to remove the line break.
- On the Excel ribbon, go to the tab “Ablebits Data”, then to the option “Text Group” and click on the button “Convert” .
- On the panel “Convert Text” select radio button “Convert line break to “, enter “Replacement” in the field and click “Convert”.
Here, each line break is replaced by a space, so you need to place the mouse cursor in the field and press the Enter key.
When using these methods, you will get a table with neatly organized data.
