An address can be defined as a method used to describe a location, by referencing other existing features.
If you are working in Excel, there are several ways to Separate Addresses in Excel, making it easy to sort or find specific address information.
A typical address format usually consists of the following elements:
The first three elements can easily be grouped into a single category called the Street Address.
While the last three elements can be categorized as Zone Information.
Most times when people are required to input their addresses, they embed all elements together as a single sentence.
For example, a very common address format is
‘1600 Pennsylvania Avenue NW, Washington, DC 20500, USA ‘.
Performing analysis on the compound address structure (like the example found above) may present a flawed result because some streets may have the same names as cities in other locations.
In this tutorial we will learn how to split (the respective elements of) addresses using:
- The Flash Fill Feature
- The Text to Column feature
- The LEFT, MID, and RIGHT Functions
So, before we proceed, let’s define some terminology that you may come across in this article:
- Columns – This is a vertical gridline in excel
- Rows – This is a horizontal gridline in excel
- Function: This is a pre-established formula that performs calculations by using specific values, called arguments, in a particular order E.g., AVERAGE, COUNT, COUNTIF
- Delimiter – This is a string of one or more characters used to specify the limits between separate, independent regions in mathematical expressions, plain texts, or any other stream of data.
The table below shows the addresses of 15 individuals who have parcels with a logistics company.
Our task today will be to separate the address elements into respective columns.
Here’s How to Separate Addresses in Excel:
Using the Flash fill Feature
I love to refer to this feature as a magical tool.
The flash fill feature is only present in Excel 2013 and later versions.
Its operation is such that it automatically detects a pattern in your data and replicates it across the cells in a selected range.
It is the best method when the address details contain several delimiters.
To use this feature, you must first confirm that your version of excel is not earlier than 2013 and secondly ensure that it has been activated in your Excel setup.
To turn on the ‘Flash fill’, simply follow these steps:
- Click on the File tab
then select Options - An ‘Excel Options’ box appears
- Click on the Advanced option
- Navigate to the Editing Options, and check the ‘Enable Autocomplete for Cell Value’ and ‘Automatically Flash Fill’ checkboxes to turn on Flash Fill.
Great! So, let’s see the steps on how to use the Flash fill.
Step 1: Label the columns where you wish to display the separated data.
In our example, we labeled Columns C, D, E, and F as Street address, City, State, and Zip Code respectively.
Step 2: Manually type in the correct separated info in the first two rows of the designated columns.
Note:
- Always ensure that the pattern is the same
- Sometimes, when typing zip codes or whole numbers that begin with zero (eg. 012), excel removes the zero from the beginning. To retain this Zero, you will need to add an apostrophe before the code/number (‘012)
Step 3: On the first designated column (Street Address), select the last filled cell (In our example this will be Cell C3)
Step 4: Click on the Data tab
Step 5: Click on the Flash Fill Option
Step 6: Repeat steps 3 to 5 for the remaining columns
You will notice that the cells get automatically filled up:
Using Text to Column Feature
This feature is used to split text data from one column into multiple columns using a delimiter.
This delimiter can be a fixed width, comma (,), period (.), or other characters. To use this feature simply follow the steps below:
Step 1: Label the columns where you wish to display the separated data. In our example, we labeled Columns C, D, E, and F as Street address, City, State, and Zip Code respectively.
Step 2: Select the Address range you wish to split (that is B2:B16)
Step 3: Click on the Data tab
Step 4: Navigate to the Data Tools section and select the Text to Column option
A ‘Convert Text to Columns Wizard’ pops up. This wizard has three steps, and you can easily navigate the steps using the Next and Back button
Step 5: In step 1 of 3, check the radio button before the ‘Delimited’ option
Step 6: Click on the Next button
Step 7: Step 2 of 3 is displayed
Step 8: In steps 2 of 3 check the box next to the desired delimiter. In our example, only the comma (,) box will be selected
Step 9: A preview of the separated data is displayed in the Preview sub-window
Step 10: Click on the Next button
Step 11: Step 3 of 3 is displayed
Step 12: Select your preferred column data format and change the ‘Destination’ cell to the cell where you wish to display the result.
(In our example this is C2. Hence, we assign our destination value as $C$2).
Note: Select
- General – To maintain a general data format
- Text – To convert the data to a text format
- Date – To convert the data to a date format
- Do not Import Column (skip) – To ensure that the result data gets displayed from the column where the analyzed data is found.
Step 13: Click on the Finish button
You will notice that the Street Address and City details are correctly displayed in the first two columns C and D.
However, Column E which is assigned to capture the State info contains both the State and Zip code details displayed.
This is because in the original addresses given, the chosen delimiter (,) is not present between the State and Zip code details.
Hence, we will need to apply another delimiter type to separate the state and zip codes.
If you carefully observe the records displayed in column E, you will agree with me that a space delimiter can be applied to separate the States from Zip codes.
Step 14: Select the range in column E which you desire to split (In our example, this is E2:E16)
Step 15: Click on the Data tab and then the Text to Column option
A ‘Convert Text to Columns Wizard’ pops up.
Step 16: In step 1 of 3, check the radio button before the ‘Delimited’ option
Step 17: Click on the Next button
Step 18: Step 2 of 3 is displayed
Step 19: In steps 2 of 3 check the box next to the desired delimiter (space).
Step 20: A preview of the separated data is displayed in the Preview sub-window
Step 21: Click on the Next button
Step 22: Step 3 of 3 is displayed
Step 23: In the Column data format section, tick the radio button before the Do not Import Column (Skip)
Step 24: Click on the finish button
Step 25: A Microsoft Excel notification pops up requesting a confirmation if you wish to replace the data already existing in the column.
Step 26: Click OK
You will notice that the State and Zip code column are correctly filled
Using the LEFT, RIGHT and MID Function
Here, we will combine three functions to separate the elements in an address in excel.
They are the LEFT function, RIGHT function, and MID function.
Our approach will be to use the LEFT function to extract the Street Address then, we will apply the MID function to get the City and State details, and finally, we will use the RIGHT function to extract the Zipcodes.
Now let’s explain how each function operates:
- The LEFT function returns the first character or characters in a text string from the left based on the number of characters you specify. The left function has a syntax of:
LEFT(text, [num_chars]
Where:
- The text argument – refers to the text string which contains the characters you wish to extract
- The num_chars argument – refers to the number of characters you want the RIGHT to return from the text. This is an optional parameter
- The RIGHT function returns the first character or characters in a text string from the right based on the number of characters you specify. The right function has a syntax of:
RIGHT(text,[num_chars])
Where:
- The text argument – refers to the text string which contains the characters you wish to extract
- The num_chars argument – refers to the number of characters you want the RIGHT to return from the text. This is an optional parameter
- The MID function returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. The MID function has a syntax of:
MID(text, start_num, num_chars)
Where:
- The text argument – refers to the text string which contains the characters you wish to extract
- The start_num argument – refers to the position in within_text where you want to start your search from. This is a required parameter
- The num_chars argument – refers to the number of characters you want the MID to return from the text
So, let’s get started.
As mentioned earlier, we will be extracting the Street Address first.
Extracting the Street Address
To extract the street address, we will utilize the LEFT function =LEFT (text, FIND (find_text, within_text, [start_num])
Step 1: Click the cell where you wish to display your result (In our example, Cell C2)
Step 2: Type in the equal to sign (=)
Step 3: Type the function LEFT
Step 4: Input an opening parenthesis ‘(‘
Step 5: Select the text or reference cell which contains the string you wish to work with. In our example this is B2
Step 6: type in a comma ‘,’
Step 7: Type the function FIND
Step 8: Input an opening parenthesis ‘(‘
Step 9: Input the find_text value in a double quote. In our example, this is “,”
Step 10: Select the within_text (that is the reference cell). In our example this is B2
Step 11: Input your start number. This is the position in within_text where you want to start your search from. In our example, we will use a start_number of – 1 so that every character before the “,“ is extracted.
=LEFT(B2,FIND(“,”,B2)-1)
Step 12: Press the Enter key on your keyboard
In summary, the formula we just used, instructs excel to FIND the first comma (,) in the address given, and then extract all details found (Street Address) before the comma (,).
Step 13: Select Cell C2. You will notice a square at the bottom right (that is the fill handle)
Step 14: Double click on the fill handle or drag it down to the last record to copy the formula across the range
You will notice that the Street Address for all records have been automatically updated in the C column
EXTRACTING CITY
To extract the city detail, we will utilize the MID function
= MID(string, FIND(delimiter, string)+1, FIND (delimiter, string, FIND (delimiter, string)+1) – FIND (delimiter, string)-1)
- Here we use the FIND function to determine the position of the first comma (“,”) to which you add 1 because you want to start with the character that follows the comma. Hence, you get the start_num argument of your Mid formula: FIND(“,”,B2)+1
- Next, get the position of the 2nd comma character by using nested FIND functions. This instructs Excel to start searching from the 2nd comma: FIND(“,”,B2,FIND(“,”,B2)+1)
To determine the number of characters to return, we need to subtract the position of the 1st comma from the position of the 2nd comma, and then subtract 1 from the result because we don’t want any extra spaces displayed in our result. Thus, the num_chars argument: FIND (“,”, B2, FIND (“,”,B2)+1) – FIND (“,”,B2)
Putting all these arguments together, our MID function will be:
=MID(B2, FIND(“,”,B2)+1, FIND (“,”, B2, FIND (“,”,B2)+1) – FIND (“,”,B2)-1)
Great! So, let’s continue with our steps
Step 1: Click the cell where you wish to display your result (In our example, Cell D2)
Step 2: Input the MID function displayed above
Step 3: Press the Enter key on your keyboard
Step 4: Select Cell D2. You will notice a square at the bottom right (that is. the fill handle)
Step 5: Double click on the fill handle or drag it down to the last record to copy the formula across the range
EXTRACTING STATE
To extract the state detail, we will also manipulate the strings by utilizing the MID function.
In coming up with the MID function, first we need to evaluate our set of addresses to figure out what is consistent, especially as it relates to the states.
From our data set, we can identify the following metrics to be consistent:
- The states are represented by 2 letters
- The states come 2 spaces after the last comma on the address
With these metrics identified, we need to have a formula that finds the number of commas using the LENGTH function.
=LEN(B2)-LEN(SUBSTITUTE(B2,”,”,””))
Next, we would substitute the last comma (,) with a pipe (|) to serve as a marking for us to do a search on within a MID function.
= SUBSTITUTE(B2,”,”,”|”,LEN(A2)-LEN(SUBSTITUTE(B2,”,”,””)))
Next, we would search for the substituted symbol (|) within the substituted text, and apply the search within a MID function and indicate the length of the desired data to be extracted, as well as the number of characters to be extracted after the symbol ‘|’
=MID(B2, FIND(“|”,SUBSTITUTE(B2,”,”,”|”,LEN(A2)-LEN(SUBSTITUTE(B2,”,”,””))))+2, 2)
Step 1: Click the cell where you wish to display your result (In our example, Cell E2)
Step 2: Input the MID function displayed above
Step 3: Press the Enter key on your keyboard
Step 4: Select Cell E2. You will notice a square at the bottom right (that is. the fill handle)
Step 5: Double click on the fill handle or drag it down to the last record to copy the formula across the range
EXTRACTING ZIP CODE
To extract the zip codes, we will utilize the RIGHT function. Since zip codes are usually 5-digit codes, we can easily use the RIGHT function to count the last 5 characters in our Address
RIGHT(text,[num_chars])
Step 1: Click the cell where you wish to display your result (In our example, Cell F2)
Step 2: Type in the equal to sign (=)
Step 3: Type the function RIGHT
Step 4: Input an opening parenthesis ‘(’
Step 5: Select the text or reference cell which contains the string you wish to work with. In our example this is B2
Step 6: type in a comma ‘,’
Step 7: Input the num_chars’. In our example, this will be 5 because we only want Excel to extract the first five characters of the address from the rear (Right).
=RIGHT(B2,5
Step 8: input the closing parentheses ‘)”. =RIGHT(B2,5)
Step 9: Press the Enter key on your keyboard
Step 13: Select Cell F2. You will notice a square at the bottom right (that is, the fill handle)
Step 14: Double click on the fill handle or drag it down to the last record to copy the formula across the range
You will notice that the Zip code for all records have been automatically updated on the C column.
Conclusion
In this tutorial, we learned how to separate addresses in excel into designated columns.
You would recall that at the introductory stage of this tutorial, I had stated that splitting/ separating addresses into their respective element will allow us to derive insightful information from any data.
Now let’s reconsider the example cited above.
After extracting the Street Address, City, State, and Zip codes, we can simply filter the data using the State column to ascertain how many deliveries we have in each state.
In addition, the method you choose to use when faced with a similar challenge will depend on the structure of the data and your preference amongst the outlined functions.
