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

{ 1 comment… read it below or add one }

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

Leave a Comment