How to format Addresses in Excel
By
SpreadCheaters
Here we have a dataset above which contains ten random addresses, we will be separating the addresses into multiple columns using some formulas. Step by step procedure is given below, let’s have a look at the dataset first.
When working with Excel, formatting addresses can be a bit tricky. However, with a few simple tips and tricks, you can easily format addresses in Excel. In this tutorial we will learn how to format addresses in Excel.
Step 1 – Add the 1st Separator
– To add the first Separator, type in the formula.
– Syntax of the formula:
=FIND(“,”,Cell_with_Address)
– In our case the formula will be
=FIND(“,”,A2)
Step 2 – Add the 2nd Separator
– To add the second Separator, type in the formula.
– Syntax of the formula:
=FIND(“,”,Cell_with_Address, 1st_separator + 1)
– In our case the formula will be
=FIND(“,”,A2,B2+1)
Step 3 – Add the 3rd Separator
– To add the third Separator, type in the formula.
– Syntax of the formula:
=FIND(“,”,Cell_with_Address, 2nd_separator + 1)
– In our case the formula will be
=FIND(“,”,A2,C2+1)
Step 4 – Type the formula for the Street Address.
– To find the Street type the formula in the cell
– Syntax of the formula is
=LEFT(Cell_with_Address,1stSeparator – 1)
– In our case the formula is
=LEFT(A2,B2-1)
Step 5 – Type the formula for the City name
To find the City type the formula in the cell
Syntax of the formula is
=MID(Cell_with_Address,1stSeparator + 1,3rdSeparator – 2ndSeparator – 5 )
In our case the formula is
=MID(A2,B2+1,D2-B2-5)
Step 6 – Type the formula for the State name.
– To find the State, type the formula in the cell
– Syntax of the formula is
=MID(Cell_with_Address,2nd_Separator + 1,3rd_Separator – 2nd_Separator – 1 )
– In our case the formula is
=MID(A2,C2+1,D2-C2-1)
Step 7 – Type the formula for the Zip Code.
– To find the Zip code, type the formula in the cell
– Syntax of the formula is
=MID(Cell_with_Address, LEN(Cell_with_Address) – 3rd_Separator)
– In our case the formula is
=RIGHT(A2,LEN(A2)-D2)
Step 8 – Format the rest of the Addresses automatically
– Select all the cells with formula.
– Drag the cells from bottom right to the rest of the cells.
– Formatted addresses will appear automatically.
