How to format Addresses in Excel

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:
– 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:
– 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:
– 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
– 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