Hi fellow Excel Addict,
Depending where you live and when you are reading this newsletter, it may be Fall or Summer. It's still summer in Newfoundland — at least for another few hours. We've had a great summer by Newfoundland standards. I especially enjoyed the lack of mosquitoes. I don't remember another summer where I have not been bitten by mosquitoes.
We are still getting a few warm days (i.e. 20°C/68°F) but things are gradually cooling down here as we get closer to fall. This is great running weather though. Especially for all the hill training I've been doing in preparation for my Cape to Cabot (C2C) run on October 16th.
Next week Tina and I are heading to Montreal for a little vacation. We were there in 2015 and loved it so much we wanted to go again. So far our plans include an Adele concert and a Montreal Canadians NHL hockey game. We visited some of the main tourist attractions last time, so now we are looking for new things to experience. Any suggestions?
Since it's only a few weeks away from C2C, we will definitely be doing some running in Montreal, maybe on Mount Royal or at the Circuit Gilles-Villeneuve.
In today's 'Excel in Minutes' tip I will be showing you how to 'Split Multi-Row Cell Contents Into Separate Cells'. I hope you find it helpful.
Please feel free to share it with your friends—chances are they will too.
If you missed my 'Excel in Seconds' newsletter from Tuesday, I showed you how to 'Sum Just The Positive or Negative Numbers In A Range'. You can read the tip here.
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Split Multi-Row Cell Contents Into Separate Cells
You may have received some files where the contents in each cell should actually be in separate cells.
Other times, data in an Excel worksheet cell may have been manually broken onto separate lines within the cell by inserting line breaks. You can insert a line break within a cell by holding down the ALT key and pressing Enter where you want to split the cell contents.
For one or two cells, you could manually cut or copy the data to separate cells. But what if you have a worksheet with hundreds of cells with multiple lines and you want to put the contents from each line into separate cells. Doing this manually for a lot of cells would be very slow and tedious.
I hate slow and tedious and I'm guessing you do too.
So I'd like to share a couple of methods you have to make this task a lot less painful for you.
Which method you choose will depend on how you want the data organized after it has been split.
Method 1: Use MS Word to Split Contents onto Separate Rows
This is a fast and simple method that I find effective when I want to split each row of a multi-line cell onto separate rows in the same column.
There are just four simple steps!
1) Copy (CTRL+C) the column of data from Excel;
2) Open Microsoft Word or Outlook and paste (CTRL+V) the data into a blank document;
3) The data in Word/Outlook is still highlighted, so simply click Copy (CTRL+C);
4) Switch back to Excel and paste (CTRL+V) into a blank column. Each of the lines that were combined in a single cell will be split onto separate rows in the same column.
Method 2: Use Text to Columns to Split Cells into Separate Columns
If you have multi-line cells that you need to have in separate columns, you can use Excel's 'Text to Columns' feature to split each line in the cell across separate columns in the same row.
1) Select a single column. Make sure there is no data in the columns to the right of the data you want to split. If your worksheet has multiple columns that you need to fix, you may want to copy each column to a different worksheet to work on;
2) From the Data tab select Text to Columns;
3) On Step 1 of the Text to Columns Wizard (see image below), select the Delimited option and click Next>;
4) On Step 2 of the Wizard, in the Delimiters section, select Other and clear check marks from any other delimiters;
5) Click in the box to the right of Other and press Delete to clear anything that may be in there;
6) With the cursor in the Other field, press CTRL+J to enter a line break character. It may not appear that you've put anything in the the box but look closely and you will see a small dot blinking in the box. That is actually the top of the blinking text cursor that was pushed down by the line break character. (Thanks to Excel MVP, Rick Rothstein for pointing that out to me);
7) By default, Text to Columns splits data across the current column and columns to the right. However, you can use the Destination field to choose a different location to split the data. This will preserve the original data;
8) If all of the data in the cells you are splitting is Text, you can click the Finish button at this point (i.e. at step 2 of the Wizard). However, if there are dates or other numeric data in the cells, click the Next> button and, in step 3 of the Wizard, select each column in the Data Preview area, choose a desired format (General, Text, Date), then click Finish.
The final result is each line from each cell has been split out across multiple columns.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630