• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Resolving text-to-columns with line breaks in Excel

Get PDF file

by on
in Excel Training,Office Technology

Q. I have a spreadsheet where three address lines are separated by a line break. How do I use text-to-columns to separate the lines into columns?

A. Each character that can be recognized by your computer has a number associated with it. The line break within a cell to make content appear on different lines is represented by CHAR(10). So, using the SUBSTITUTE function, you can replace the line break with something like a comma or a slash and use it as a delimiter when doing a Text to Columns operation.

In the example above, you substitute the line break with a “/”. After Text to Columns, you can separate the city, state and ZIP information. The formula below is =SUBSTITUTE(A1,CHAR(10),”/”).

{ 17 comments… read them below or add one }

Mary Butlin April 11, 2018 at 9:08 am

Brilliant solution thank you. I couldn’t get the text to columns to work initially as it simply lost the data on lines 2, 3, 4 etc even though the data looked fine and had all the “/” in place. But when I did another column and used “clean(a2)” then copies and pasted values again it all worked perfectly.

Reply

annonymous April 12, 2018 at 5:56 am

Thank you very much for the tip! I was having the same issue and flipping out because Google wasn’t able to provide me with any solutions without possibly, third-party software or add-in… After reading this, i tried the CLEAN function, and the tool is working again.

Reply

AYN March 5, 2018 at 3:41 am

i cannot seem to seperate names with text to column. it only seperates names and surnames when there is also a middle name. other wise, it does not. please help me.

Reply

jjong August 28, 2017 at 12:42 am

hi all, can’t use alt-0010 with excel 2016.

Reply

Ed September 21, 2017 at 4:27 pm

I am also seeing that alt + 0010 does not work for text to rows. I am not able to make that substitute work either. Does anyone have a sample worksheet that would show this?

Reply

shills April 18, 2018 at 10:22 am

If you are using laptop please connect to an external keyboard and try from the number keypad ALT +0010 it will work perfectly.

Reply

Kathryn Lambkin August 24, 2017 at 11:17 pm

If you copy and paste the formula from above, you make need to tweak it by replacing the double-quote marks character with the correct one, eg. =SUBSTITUTE(A1,CHAR(10),”/”)

Reply

Dhananjay Chuabey June 30, 2017 at 2:04 am

Thanks a lot Andrew. This works.

Reply

Rufus October 25, 2016 at 5:20 pm

Hi there
I use Ctrl+J with text to column – works every time for me.

Reply

Scarlett September 11, 2017 at 4:44 am

Works perfectly! Thanks!!

Reply

Elry Allen September 29, 2016 at 9:15 am

Thanks, tried a number of things like text to columns using the other delimiter of Alt “0010” or Alt “J” – they did not work, but your solution was perfect with the SUBSTITUTE function to replace the hard return with a “,” then I could do text to columns….

Reply

Chris May 19, 2016 at 5:09 pm

Thanks Andrew. When I tried your method on my cells (each with for lines of information), it ended up deleting the last 3 and only giving me the first line. Any idea why that would be?

Reply

Kimber January 31, 2019 at 9:48 am

Chris, 3 years later we have this same problem as you, and I don’t see any comments that resolved it. Did you ever figure out what is happening? We did everything recommended and it’s just not working on the file we received. (It does work on new data that we create ourselves, with the line breaks.)

Reply

Stan August 19, 2015 at 11:15 am

Thanks Andrew! saved me a bunch of effort with screwy formatted data.

Reply

Andrew June 4, 2015 at 1:22 am

Try this (Re posting from another site by a user called Nick)

Step 1) select the column

Step 2) go into text to column

Step 3) tick other and in the box type 0010 while Holding the Alt key. (it looks as if youve typed nothing, dont worry)

Step 4) hit next and finish

The reason this works is that 0010 is the ASCII code for a line break. (note that the numbers must be typed on a numberpad).

Reply

Dustin February 2, 2016 at 2:15 pm

Thanks Andrew – you saved me a bunch of time!

Reply

manvi November 28, 2016 at 7:54 am

Thanks a lot Andrew… this works…. you have solved a really big issue… please keep on sharing the information. it helps

Reply

Leave a Comment