Kim has a distribution list in Outlook with 130+ names. She wants those names in Excel. When she copies and pastes, everything ends up in a single cell.
In this episode, a simple set of steps to split the distribution list into rows and then columns for Name and E-Mail address.
These steps won't (yet) work on a Mac. You are using the Get & Transform tools (also known as Power Query) on the Windows version of Excel.
In this episode, a simple set of steps to split the distribution list into rows and then columns for Name and E-Mail address.
These steps won't (yet) work on a Mac. You are using the Get & Transform tools (also known as Power Query) on the Windows version of Excel.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2282.
This is a great one.
Paste an Outlook distribution list into Excel and break it into useful rows.
Welcome back to this MrExcel netcast. I'm Bill Jelen.
I was down in Vero Beach doing a seminar and Kim showed up and had a problem.
She said, look, I have a huge outlook distribution list and I copy it from Outlook and I paste it to Excel and it pastes like this all into a single cell.
One really long cell. 4274 characters. How can I use this?
Oh my God Kim. What version of Excel do you have? Office 365.
I said that's a beautiful thing. Here's what we're going to do.
We're going to make a table out of this.
I'm going to insert one row here and I'm just going to call it Kim, right?
I'm going to choose those two cells and Ctrl+T or Home, Format as Table.
Doesn't matter what style I use. Alright, we just need to make it into a table.
Why make it into a table?
Because back here on the Data tab this From Table works beautifully with tables, right?
So I'm going to choose From a Table or Range and it's going to open the Power Query editor with one big long string there.
So I'm going to choose that ccolumn.
We're going to do a couple of things here on the Home tab, going to replace values. Change each occurrence of semi-colon space.
Semi-colon space to just semi colon click OK.
I'm going to take that final right bracket like that and get rid of it so replace values this time.
Right bracket replaced with nothing, click OK.
All right, and then I'm going to break it at the semicolons and I can't see many semicolons here, but there's a bunch of them.
Alright so we select the data.
It is still selected to Split Column by Delimiter.
They're not going to be able to figure it out in this case, 'cause there's too many weird things in there, so we're going to Split by semi colon, click Advanced Options.
This is the beautiful thing right here and split into rows.
Split into Rows, click OK.
and now we have rows and rows and rows of data with Name, left less-than sign, and then the email address.
Alright, so back in to replace values and we're going to do Space less-than.
Replace with less-than, click OK.
And then back into split column by delimiter.
This time they were able to get it that it was custom and we're going to say, well, just at the leftmost delimiter in case we happen to have someone who had a less-than sign in their email address, which is probably not legal. Kim.1 column.
Let's double-click up here and call it Name and then here double-click and call it email right and then on the Home tab click Close and Load. We got a brand new sheet of the original data here.
That's The Ugly data. Here is the cleaned up data like that.
And the beautiful thing about this right now.
Kim can take this data.
Copy this data control C and go wherever she needs to go.
When I'm going to suggest is that this should become Kims Awesome Utility For Dealing With Distribution Lists and what Kim is going to do is next time she gets a distribution list come and choose that one cell right there and let me copy another distribution list here.
This is a shorter distribution list this time control C and we're going to paste it right to that one cell Control V and then we have to do all the process again. Here's the awesome thing.
Look over here on the right hand side of the screen.
This Queries and Connections panel.
I need to make it a little bit wider so I can see the refresh icon wider still. And I click refresh.
And Bam! I get 5 rows loaded instead of 115 rows loaded.
Now - now that I see this, I see that we're getting an extra row here, so I'm going to right-click.
And Edit.
And we will add a extra step here that says get rid of that null and the last one.
Close and Load, and that's the beautiful thing about Power Query.
You realize you made a mistake the first time.
It will fix itself, or you can go back and fix it later. This is just an amazing tool.
It debuted in Excel 2016 originally in Excel 2016 was kind of over here. Get & Transform.
In Office 365. They moved it over to the left.
Excel 2019 still there.
Now this is windows only.
It might be coming to the Mac sometime next decade, but for right now, if you have Windows, it is just a beautiful, beautiful thing.
If you like this tip, please click that Subscribe button and ring that bell.
Post any comments or questions in the comments below, check out my new book, MrExcel LX the Holy Grail of Excel Tips.
Thanks to Kim for showing up in my seminar and I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
This is a great one.
Paste an Outlook distribution list into Excel and break it into useful rows.
Welcome back to this MrExcel netcast. I'm Bill Jelen.
I was down in Vero Beach doing a seminar and Kim showed up and had a problem.
She said, look, I have a huge outlook distribution list and I copy it from Outlook and I paste it to Excel and it pastes like this all into a single cell.
One really long cell. 4274 characters. How can I use this?
Oh my God Kim. What version of Excel do you have? Office 365.
I said that's a beautiful thing. Here's what we're going to do.
We're going to make a table out of this.
I'm going to insert one row here and I'm just going to call it Kim, right?
I'm going to choose those two cells and Ctrl+T or Home, Format as Table.
Doesn't matter what style I use. Alright, we just need to make it into a table.
Why make it into a table?
Because back here on the Data tab this From Table works beautifully with tables, right?
So I'm going to choose From a Table or Range and it's going to open the Power Query editor with one big long string there.
So I'm going to choose that ccolumn.
We're going to do a couple of things here on the Home tab, going to replace values. Change each occurrence of semi-colon space.
Semi-colon space to just semi colon click OK.
I'm going to take that final right bracket like that and get rid of it so replace values this time.
Right bracket replaced with nothing, click OK.
All right, and then I'm going to break it at the semicolons and I can't see many semicolons here, but there's a bunch of them.
Alright so we select the data.
It is still selected to Split Column by Delimiter.
They're not going to be able to figure it out in this case, 'cause there's too many weird things in there, so we're going to Split by semi colon, click Advanced Options.
This is the beautiful thing right here and split into rows.
Split into Rows, click OK.
and now we have rows and rows and rows of data with Name, left less-than sign, and then the email address.
Alright, so back in to replace values and we're going to do Space less-than.
Replace with less-than, click OK.
And then back into split column by delimiter.
This time they were able to get it that it was custom and we're going to say, well, just at the leftmost delimiter in case we happen to have someone who had a less-than sign in their email address, which is probably not legal. Kim.1 column.
Let's double-click up here and call it Name and then here double-click and call it email right and then on the Home tab click Close and Load. We got a brand new sheet of the original data here.
That's The Ugly data. Here is the cleaned up data like that.
And the beautiful thing about this right now.
Kim can take this data.
Copy this data control C and go wherever she needs to go.
When I'm going to suggest is that this should become Kims Awesome Utility For Dealing With Distribution Lists and what Kim is going to do is next time she gets a distribution list come and choose that one cell right there and let me copy another distribution list here.
This is a shorter distribution list this time control C and we're going to paste it right to that one cell Control V and then we have to do all the process again. Here's the awesome thing.
Look over here on the right hand side of the screen.
This Queries and Connections panel.
I need to make it a little bit wider so I can see the refresh icon wider still. And I click refresh.
And Bam! I get 5 rows loaded instead of 115 rows loaded.
Now - now that I see this, I see that we're getting an extra row here, so I'm going to right-click.
And Edit.
And we will add a extra step here that says get rid of that null and the last one.
Close and Load, and that's the beautiful thing about Power Query.
You realize you made a mistake the first time.
It will fix itself, or you can go back and fix it later. This is just an amazing tool.
It debuted in Excel 2016 originally in Excel 2016 was kind of over here. Get & Transform.
In Office 365. They moved it over to the left.
Excel 2019 still there.
Now this is windows only.
It might be coming to the Mac sometime next decade, but for right now, if you have Windows, it is just a beautiful, beautiful thing.
If you like this tip, please click that Subscribe button and ring that bell.
Post any comments or questions in the comments below, check out my new book, MrExcel LX the Holy Grail of Excel Tips.
Thanks to Kim for showing up in my seminar and I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
