Welcome to another episode of the MrExcel Podcast, where we share useful Excel tips and tricks to help you become an Excel pro. In today’s episode, we have a surprising solution for splitting an Outlook distribution list in Excel. This is a common problem faced by many Excel users, but with our simple method, you’ll be able to break the list into useful rows in no time.
During a recent seminar in Vero Beach, a participant named Kim approached me with a dilemma. She had a large Outlook distribution list that she copied and pasted into Excel, only to find that it all ended up in a single cell with 4274 characters. She was using Office 365 and was unsure of how to use this data. But fear not, Kim, because we have the perfect solution for you.
First, we’ll make a table out of the data by inserting a row and formatting it as a table. This will allow us to use the /”From Table/” function in the Data tab, which works beautifully with tables. Next, we’ll use the Power Query editor to split the data into rows. We’ll replace the semi-colon space with just a semi-colon and get rid of the final right bracket. Then, we’ll split the data at the semicolons and select the option to split into rows. This will give us rows of data with names and email addresses.
But we’re not done yet. We’ll replace the space less-than with just a less-than and split the column by delimiter again, this time selecting the leftmost delimiter. And just like that, we have a clean and organized list of names and email addresses. The best part? This process can be easily repeated for any future distribution lists by simply refreshing the data. No more manual splitting and formatting needed.
Power Query is truly a game-changer in Excel. It was first introduced in Excel 2016 as /”Get & Transform/” and has since been moved to the left in Office 365. It’s also available in Excel 2019 for Windows users. Unfortunately, it’s not yet available for Mac users, but we hope it will be in the near future. If you’re a Windows user, you’ll definitely want to take advantage of this amazing tool.
If you found this tip helpful, don’t forget to hit that Subscribe button and ring the bell to be notified of our future episodes. And as always, feel free to leave any comments or questions in the comments section below. Also, be sure to check out my new book, /”MrExcel LX: The Holy Grail of Excel Tips/”. Thank you to Kim for bringing this problem to my attention and thank you for tuning in. We’ll see you next time for another netcast from MrExcel.
Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
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.
This video answers these common search terms:
how to make outlook distribution list into excel
how to create a distribution list from outlook to excel
parse an outlook distribution list to rows in excel
export outlook distribution list to excel
split outlook contact group to excel rows
convert outlook group to excel spreadsheet
extract members from outlook distribution list to excel
import outlook contact group into excel
outlook distribution list to excel converter
export email group to excel from outlook
transfer outlook contact group to excel rows
Table of Contents
(0:00) Problem Statement: Split Outlook Distribution List into Excel Rows
(0:40) Make one-row table
(1:05) Open Power Query Editor
(1:20) Replace in Power Query
(1:47) Split by semi-colon in Power Query to Rows
(2:21) Split by less-than sign
(2:45) Return clean date to Excel
(3:11) Paste future distribution list to A2 & Refresh query
(3:45) Editing an existing query in Power Query
(4:18) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154111/
Take the opportunity to connect and share this video with your friends and family if you find it useful.
Keywords: Microsoft Excel, Excel, Bill Jelen, MrExcel, outlook distribution list, parsing data in Excel, parsing the Outlook Distribution list in Excel, Copy Outlook Distribution List to Excel, Using Get & Transform in Excel, Split by Delimiter to Rows, how to, excel tutorial, bill jelen power query, microsoft office, Excel में Outlook वितरण सूची पार्स करना, تحليل قائمة توزيع Outlook في Excel, outlook distribution list export, mr excel, mail merge in outlook