How To Separate Data In A Cell Based On Line Breaks

2017-03-18

Get The Workbook

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:

John MacDougall
123 Fake Street
Any Town
Canada

Rather than like this:

John MacDougall, 123 Fake Street, Any Town, Canada

 

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.

  1. Select all the data containing multiple in-cell lines which you want to convert into single line cells.
  2. Go to the Data tab in the ribbon.
  3. Select Text to Columns in the Data Tools section.

 

 

Text to Columns wizard step 1.

  1. Select Delimited option under Original data type.
  2. Press the Next button.

 

 

Text to Columns wizard step 2.

  1. Select Other from the Delimiters options and deselect all the rest of the options. In the input box to the right of Other press Ctrl + J to insert a line break as your delimiter.
  2. You should see dividers appear in the Data preview pane where there are line breaks in your data.
  3. Press the Next button.

 

 

Text to Columns wizard step 3.

  1. Use the range selector icon to choose a destination for the divided data. Select a cell outside of the original data range to keep a copy of the original data.
  2. Check the Data preview to make sure the text to column is correct.
  3. Press the Finish button.

 

 

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!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This