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),”/”).
- Tip Card: Business Management Daily's Favorite Keyboard Shortcuts
- Date troubles in Excel? Inconsistent date formats in column are frustrating
- When printing Excel work sheets, do you get blank pages?
- Paste default for Excel charts
- One of the most frustrating things when creating a Pivot Table is a value field