• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Resolving text-to-columns with line breaks in Excel

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

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/33958/resolving-text-to-columns-with-line-breaks-in-excel "

Leave a Comment