Excel does not recognize line breaks until I double click

Question

Hi Everyone,&nbsp;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 double click into the cell and then back out (hit Enter, Tab, etc), Excel applies line breaks automatically where they are at in the web, which then allows me to break out addresses using Text to Columns. I'm looking for a way to simulate that process without manually clicking into each cell (my table has 100+ rows) to show the line breaks that already exist. How can I simulate this process? Is there a quick shortcut I can do, macro, etc?&nbsp;I have tried Wrap Text and fit column width but that does not solve the issue. Attached is an example file where you can replicate the issue.

hansvogelaar · Accepted Answer

Rmcclung&nbsp;
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

Forum Discussion

Excel does not recognize line breaks until I double click

2 Replies

Resources