You sometimes get text with line breaks when copying a text from a website or word document. Although line breaks can be useful for a better layout, they can also make Excel tables look weird. In this guide, we’re going to show you how to Remove Line Breaks in Excel in two ways.
Find and Replace
You can use Excel’s Find and Replace feature to remove line breaks. The tricky part is entering the line break character into the Find what box. You have 2 options to do this:
- Type in Ctrl + J
- Type in Alt + 010 (not 10, exact characters of 010)
Both combinations can produce a new line character.
If you want to remove line breaks and merge the lines only, click the Replace/Replace All button. However, the result may not look great if you have rather long strings of text.
Replacing new lines characters with spaces is a common example. With this, you can convert multilevel phrases into a single row. After entering a new line character, enter a space character into the Replace with box and click the Replace/Replace All button.
Removing Line Breaks using Formulas
An alternative way is to use formulas to remove line breaks. The logic behind this approach is similar to the Find and Replace method. You can use the SUBSTITUTE function to replace new line characters with the character(s) you want.
You will need to specify a new line character for this method too. The CHAR function, which returns a character based on the input number, can also be used for this purpse. You need to use the number value (ASCII) that represents the character. The problem here is that there are 2 numbers which represent a new line (carriage return/line feeds) for Windows and UNIX systems.
- Windows: 10
- UNIX: 13
Thus, you may want to use 2 SUBSTITUTE functions to address both characters. Here is an example:
Both new line characters will be replaced with space characters.
