Resolving text-to-columns with line breaks in Excel — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • 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),”/”).

{ 12 comments… read them below or add one }

jjong August 28, 2017 at 12:42 am

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


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?


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),”/”)


Dhananjay Chuabey June 30, 2017 at 2:04 am

Thanks a lot Andrew. This works.


Rufus October 25, 2016 at 5:20 pm

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


Scarlett September 11, 2017 at 4:44 am

Works perfectly! Thanks!!


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


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?


Stan August 19, 2015 at 11:15 am

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


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


Dustin February 2, 2016 at 2:15 pm

Thanks Andrew – you saved me a bunch of time!


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


Leave a Comment

Previous post:

Next post: