You can specify the delimiter (such as a space, comma, or tab) and the Text to Columns would use this delimiter to split the content of the cells. Examples of this include splitting first and last names, or username and domain name in email ids. However, if you have a dataset where the delimiter is a line break (in the same cell), it gets tricky to split these multiple lines in the same cell into separate cells/columns. For example, you can have a dataset as shown below, where you need to split multiple lines in the address (separated by line breaks) into separate cells.
This would allow you to have the Name, Street, City, and Country in separate cells. So the result would look something as shown below:
Now to separate each part of the address, we can use the Text to Columns functionality (with a twist).
Using Text to Column to Split Multiple Lines in a Cell
Below are the steps that will split multiple lines in a single cell into separate cells:
Select the entire dataset that you want to split. Go to the Data tab. In the Data Tools group, click on the Text to Columns option. In the Text to Columns dialog box, in Step 1 of 3, select Delimited and click ‘Next’. In Step 2 of 3, uncheck any existing Delimiters selection, and select the ‘Other’ option. Now Use the keyboard shortcut Control + J (hold the ‘Control’ key and then press the ‘J’ key). You will not see anything in the box except a tiny blinking dot (if you look hard). Also, you will see the expected result in the ‘Data preview’ section (as shown below). Click on Next. In Step 3 of 3, change the ‘Destination’ cell to the one where you want the output. In my example, I want the result in B2, so I changed the value to $B$2. Click on Finish.
The above steps would automatically split the content of the cells based on where the line break occurs in each cell. Note that if you don’t want to keep the original data (it’s always advisable to do so though), you don’t need to specify the destination cell. It will simply overwrite the existing data and give you the result. Also, in case you already have data in the cells where you are about to get the result of Text to Columns, Excel will show you a prompt letting you know that you will be overwriting the existing data. You can choose to overwrite or cancel
How does this work? When you use the keyboard shortcut Control J, it specifies the line break as the delimiter in the Text to Columns wizard. Now, Text to Columns checks each cell for line breaks and use it to split the content into separate cells. Note that Text to Columns would always split the content of the cells in separate columns. If you want to get the result in cells in different rows, you can transpose the result (as shown here).
How to Split Cells in Excel. How to Remove Line Breaks in Excel How to Number Rows in Excel How to Quickly Combine Cells in Excel. CONCATENATE Excel Range (with and without separator).
Thanks, Abdul
