How To Separate Data In A Cell Based On Line Breaks

While entering or editing data in Excel it is possible to add line breaks in the cell using Alt + Enter. This can be a convenient way to display data in Excel, but is often not great for any type of data analysis.   For example, it might be more convenient for the reader to display an address in a cell like this: Rather than like this:   In this post I’ll show you how to separate the data in your cells based on the in-cell line breaks. This can be useful when doing any sort of data analysis on the data. In this example, we have address data which includes a person’s name, address, city and country. The data is in one cell for each person but the name, address, city and country are on different lines.  

  Select your data and open the Text to Columns wizard.  

  Text to Columns wizard step 1.  

  Text to Columns wizard step 2.  

  Text to Columns wizard step 3.  

  Now each line from the original data is in its own column allowing for easier analysis of the different data fields using pivot tables, formulas or other tools!