Resolving text-to-columns with line breaks in Excel

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