Excel - Learn How to Separate Addresses into Multiple Columns in Excel - Episode 417
Microsoft Excel Tutorial: Learn How to Separate Addresses into Multiple Columns in Excel.
Welcome back to the MrExcel netcast, where we explore all things Excel. In today's episode, we're going to dive into the world of parsing data, specifically addresses. Have you ever had a data set with addresses all in one column and needed to separate them into different columns? Well, Excel has some great tools for that and I'm going to show you how to use them.
First, let's take a look at our data set. We have names in column A and additional data in the following columns. Our goal is to break the names into first and last names. This can be tricky because some names may have multiple first or last names. To start, I'm going to insert a few extra columns just to be safe. Then, I'll select my data set and go to the Data menu to choose the Text to Columns command.
In the Text to Columns wizard, we have the option to choose if our data is delimited or fixed-width. In this case, our data is delimited by a space, so we'll select that option and click Next. On the next step, we'll specify that our delimiter is a space and uncheck the Tab key. Then, we'll leave the data format as General and click Finish. Excel will then break our data apart into separate columns.
Next, we'll need to check the column where we don't expect any data, in this case, column C. We'll hit the End key and the down arrow key to see if there is any data there. If we find any, we'll need to manually fix it by copying the last name from column C to B. This process is relatively simple when our data is separated by a space, but what about addresses with commas and spaces?
Let's say we have addresses with a city, comma, 2-letter state abbreviation, space, and zip code. In this case, it may be best to split the data into two steps. First, we'll use the Text to Columns wizard to separate the city from the state and zip code by choosing the comma as our delimiter. Then, we'll reselect the data in column B and use the Text to Columns wizard again, this time choosing the space as our delimiter.
One thing to note is that all of our data in column B starts with a space. To fix this, we'll need to go to step 3 of the Text to Columns wizard and choose "Do not import" for the first column. This will ensure that our states and zip codes are in the correct columns. And just like that, we have successfully parsed our addresses into three separate columns.
Thanks for tuning in to this episode of the MrExcel netcast. I hope you found this tutorial helpful and can use these techniques in your own data sets.
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-...
Table of Contents:
(00:00) Splitting data in one column into two
(00:29) Parsing data using Excel tools
(00:50) Using the Text to Columns command
(01:00) Choosing between Delimited and Fixed-width data
(01:45) When to choose Text format
(01:58) Checking for unexpected data
(02:14) Manually fixing errors
(03:42) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Checking for data in specific columns in Excel
Choosing the delimiter in Text to Columns wizard
Converting data to General format in Excel
Delimited vs Fixed-width data in Excel
Excel Text to Columns command
How to break a single column into two columns in Excel
Importing and not importing data in Excel Text to Columns
Manually fixing data in Excel columns
Parsing data in Excel
Splitting addresses in Excel using Text to Columns
Splitting city, state, and zip code in Excel
Splitting data for mailing purposes in Excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Sometimes you have a single column with several fields joined together. For example, you might have city, state, zip code in a single column. Episode 417 shows how to break this data into three columns using the Text to Columns command.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!