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

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

{ 5 comments… read them below or add one }

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!


Leave a Comment


Previous post:

Next post: