FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003
September 22, 2016
 

Greetings from The Excel Addict
Hi fellow Excel Addict,

Moving from Summer to FallD
epending 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. 20C/68F) 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.

'Excel Macros for Beginners' Online Video Course

Francis Hayes (TheExcelAddict.com)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.

I
f 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)
Email:  fhayes[AT]TheExcelAddict.com
 
 
x
Click here for details on my e-book BONUS


 

If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"I hated every minute of training; but I said; don’t quit.
Suffer now and live the rest of your life as a champion."

-- Muhammed Ali --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

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.

Cell Contents Split On Separate Lines in Microsoft Excel 2007 2010 2013 2016 365
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.

Create A Line Break Within A Cell in Microsoft Excel 2007 2010 2013 2016 365

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.

CLICK HERE TO DOWNLOAD A PRACTICE FILE TO FOLLOW ALONG


Method 1: Use MS Word to Split Contents onto Separate Rows

Split Multiple Line Contents To Separate Rows in Microsoft Excel 2007 2010 2013 2016 365This 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.




Split Multi-Line Cells Using Microsoft Word Or Outlook in Microsoft Excel 2007 2010 2013 2016 365

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.

Split Multi-Line Cell Contents in Microsoft Excel 2007 2010 2013 2016 365
Here's how...

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.

Split Multiple Line Contents To Separate Using Text To Columns in Microsoft Excel 2007 2010 2013 2016 365

The final result is each line from each cell has been split out across multiple columns.
Split Contents To Separate Columns in Microsoft Excel 2007 2010 2013 2016 365


 
x
Click here for details on my e-book BONUS


 
FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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