Excel does not recognize line breaks until I double click

Hi Everyone, I have a basic .xlsm file where a list of addresses is imported from the web. By default, the data is displayed in each cell bunched up with no breaks between the addresses. When you...

Example.xlsx9 KB

excel

Formulas and Functions

HansVogelaar

May 22, 2023

Rmcclung

The problem is that the addresses are separated by carriage returns (character code 13), but Excel uses line feed (character code 10) as line break. Here is a macro you can run. Select the data before you do so.

Sub SplitData()
    Application.ScreenUpdating = False
    Selection.Replace What:=vbCr, Replacement:="|", LookAt:=xlPart
    Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
    Tab:=False, SemiColon:=False, Comma:=False, Space:=False, _
    Other:=True, OtherChar:="|"
    Application.ScreenUpdating = True
End Sub

HansVogelaar

MVP

May 22, 2023

Rmcclung

Sub SplitData()
    Application.ScreenUpdating = False
    Selection.Replace What:=vbCr, Replacement:="|", LookAt:=xlPart
    Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
    Tab:=False, SemiColon:=False, Comma:=False, Space:=False, _
    Other:=True, OtherChar:="|"
    Application.ScreenUpdating = True
End Sub

Rmcclung

Copper Contributor

May 23, 2023

This worked perfectly. Thank you so much! I'll tag this as accepted.

Forum Discussion

Excel does not recognize line breaks until I double click

Resources