Rubayed Razib Suprov
In Excel, a line break is frequently used, especially in documenting addresses. Also to view long text inside a singular cell. In many cases, we may need to view the text lines in a singular line, not in broken parts. How we can find those line breaks in Excel and replace them with our desired character is discussed below in different methods with elaborate explanations with practical examples.
For demonstrative purposes, we are going to use the below dataset. Random anonymous addresses are the perfect example of having line breaks. We will remove these line breaks and replace them with different characters in detailed explanations with appropriate examples.
1. Using Find & Replace Command to Replace Line Breaks in Excel
With the help of the Find and Replace command, you can easily find your target character and replace them with your desired one.
Steps
- At first, you need to copy the cell content from the range of cells B5:B8 to the range of cells C5:C8.
- Next, select the cells that you just copied, then on the keyboard press Ctrl+H or from the Home tab, go to Find & Select > Replace.
- A new window will open named Find and Replace, from that window go to Replace tab, and in the Find what field, press ‘Ctrl+Shift+J’.
- Pressing ‘Ctrl+Shift+J’, a line break will inserts. It shows only as a tiny dot in the field.
- Then press Tab to move to Replace with In this field, type the expression with which you want to replace the line break with. In this field we will enter “– “. If you want to replace line break with nothing, then keep this field empty. Press Replace all after this.
- After pressing the Replace All, you will notice that all the content in the range of cells B5:B8 is now without line breaks and they are replaced by “-” in the range of cells C5:C8.
Note:
In some cases, especially for different versions of Excel pressing ‘Ctrl+Shift+J’ may not work. In that case, you better try pressing ‘Ctrl+J’ and vice versa.
Read More: [Fixed!] Line Break in Cell Not Working in Excel
2. Utilizing SUBSTITUTE Function to Find and Replace Line Breaks
The SUBSTITUTE function is used to find a particular character using the CHAR function and replace them with another character according to the input arguments.
Steps
- In the beginning, select the cell at C5, and enter the following formula:
=SUBSTITUTE(SUBSTITUTE(B5,CHAR(13),""),CHAR(10),",")- After the formula, you will notice that the content in cell B5 which contains line breaks now contains no line breaks and replaced line breaks with “;” in cell C5.
- Drag the Fill Handle icon at the corner of cell C5, and drag it to cell C7.
- You will notice that all the content in the range of cells B5:B8 is now replaced with line breaks with “;” in the range of cells C5:C7.
How Does the Formula Actually Work?
1. SUBSTITUTE(B5, CHAR(13),””): This function will replace line break with nothing. This part is necessary for both UNIX and WINDOW operations.
2. SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “): This formula will remove line break and replace line break with “,”.
Read More: How to Replace Line Break with Comma in Excel (3 ways)
3. Inserting Excel TRIM Function to Find and Replace Line Breaks
In this method, we are going to use the TRIM function alongside the SUBSTITUTE and the CHAR functions to replace line break with “,”.
Steps
- Select the cell C5, and enter the following formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(B5,CHAR(13),""),CHAR(10),", "))- After the formula, you will notice that the content in cell B5 which contains line breaks now contains no line breaks and replaced every line breaks with ”,” in cell C5.
- Drag the Fill Handle icon that the corner of cell C5, and drag it to cell C8.
- You will notice that all the content in the range of cells B5:B8 is now replaced with line breaks with “,” in the range of cells C5:C8.
How Does the Formula Actually Work?
1. SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “): This formula will replace line break with “,” in the case of both Windows and UNIX carriage return/ line feeds combinations.
2. TRIM(SUBSTITUTE(SUBSTITUTE(B5, CHAR(13),””), CHAR(10),”, “)): TRIM function will make sure that there will be no extra space and lines won’t join.
Read More: How to Go to Next Line in Excel Cell (4 Simple Methods)
4. Inserting CLEAN Function to Remove Line Breaks in Excel
The CLEAN function is generally used to remove all kinds of unprintable characters. As line break is such an unprintable character, we can use the CLEAN function to remove line break.
Steps
- At first, select the cell at C5, and enter the following formula:
=CLEAN(B5)- After the formula, you will notice that the content in cell B5 which contains line breaks now contains no line breaks and replaced every line breaks with a Space in cell C5.
- Drag the Fill Handle icon that the corner of cell C5, and drag it to cell C7.
- You will notice that all the content in the range of cells B5:B8 is now replaced with line breaks with a space in the range of cells C5:C7.
5. Applying Wrap Text Command to Delete Line Breaks
Normally Wrap Text command is used to show a long string of text in an Excel cell in multiple lines for better visual purposes. Unwrapping the wrapped text can remove line breaks and show long text in a single line.
Steps
- To remove the line breaks in the contents in the range of cells B5:B7, copy them first to the range of cells C5:C7.
- Then select the range of cells C5:C7. Then click on the Wrap Text icon from the Alignment group in the Home tab, which is already in grey.
- After clicking the Wrap Text button will convert all the selected cells containing line breaks to have space in the place of line breaks.
Read More: How to Put Multiple Lines in Excel Cell (2 Easy Ways)
6. Applying VBA Macro to Find and Replace Line Breaks
Using a simple VBA Macro can drastically reduce the time to replace line break with your desired character.
Steps
- First, go to the Developer tab, then click Visual Basic.
- Then click Insert > Module.
In the Module window, enter the following code:
Sub linebreak_replace()
Dim Mr As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Mr In ActiveSheet.UsedRange
If 0 < InStr(Mr, Chr(10)) Then
Mr = Replace(Mr, Chr(10), "_")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Note:
- Keep in mind that, this VBA code will apply to the whole worksheet.
- Here, “_” removes and replaces line breaks. To replace the line break with some other character, edit the part inside the Replace function.
- Then close the Module.
- After that, go to the View tab > Macros(Double click).
- After clicking View Macros, select the macro that you created just now. The name here is linebreak_replace. Then click Run.
- After clicking Run, you will notice all the content in the cell range of B5:B7 is now found and replaced line breaks with “_” in Excel.
In this ways, you can find and replace line breaks quite easily in Excel.
Read More: Excel VBA: Create New Line in MsgBox (6 Examples)
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to find and replace line breaks in Excel” is answered here in 6 different ways. Started by using the Find and Replace tool, then continued to use TRIM, SUBSTITUTE, CLEAN, CHARfunctions, and continued to use ended with using VBA Macros. Among all of the methods used here, using functions is the easier to understand and simple one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.
For this problem, a macro-enabled workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
