Find and Replace Line Breaks in Excel (6 Examples)

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.


Download Practice Workbook

Download this practice workbook below.


6 Examples to Find and Replace Line Breaks in Excel

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.

Find and Replace Line Breaks in Excel


1. Using ‘Find & Replace’ Command to Replace Line Breaks

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.

Find and Replace Line Breaks in Excel using Find and Replace tool

  • 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.

Find and Replace Line Breaks in Excel using Find and Replace tool

  • 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

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.

Find and Replace Line Breaks in Excel using Substitute function

  • 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. Use of TRIM Function to 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.

Find and Replace Line Breaks in Excel using Trim function

  • 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.
Find and Replace Line Breaks in Excel using Trim function

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. Applying CLEAN Function

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.

Find and replace line break using CLEAN function

  • 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. Utilizing ‘Wrap Text’ Command

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.

Find and replace using wrap text

  • 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. Embedding 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.

Find and replace line break using VBA macro

  • 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

Find and replace line break using VBA macro

Note:

  1. Keep in mind that, this VBA code will apply to the whole worksheet.
  2. 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).

Find and replace line break using VBA macro

  • 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.

Find and replace line break using VBA macro

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)


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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo