How to Format Addresses in Excel: 4 Easy Methods

Method 1 – Using FIND, LEFT, MID and RIGHT Functions

Steps:

Select cell D6.
Write down the following formula in the cell.

=FIND(",",C6)

Press Enter.

Select cell E6 and write down the following formula in the cell.

=FIND(",",C6,D6+1)

Press Enter.

In cell F6, write down the following formula to get the character number of the 3rd separator.

=FIND(",",C6,E6+1)

Press Enter to get its value.

Get the Home Location, select cell G6, and write down the following formula in the cell. The LEFT function will help us to get the location.

=LEFT(C6,D6-1)

Press Enter to get the Home Location.

In cell H6, use the MID function and write down the following formula to get the value of Area.

=MID(C6,D6+1,F6-D6-5)

Press Enter.

Write down the following formula using the MID function in cell I6 to get the value of the State.

=MID(C6,E6+1,F6-E6-1)

Press Enter to get the value.

In cell J6, write down the following formula to get the value of the ZIP Code.

=RIGHT(C6,LEN(C6)-F6)

Press Enter to get the value.

Select the range of cells D6:J6.
Double-click the Fill Handle icon to copy all the formulas up to row 14.

Get all of the unformatted addresses in the correct format.

Breakdown of the Formula

We are breaking down our formula for cell J6.

LEN(C6): This function returns 34.

RIGHT(C6,LEN(C6)-F6): This function returns 10457.

Method 2 – Applying Combined Formula to Format Addresses

Steps:

Select the range of cells B5:J15.

Convert the data range into a table, press ‘Ctrl+T’.
A small dialog box called Create Table will appear.
Check My table has headers option and click OK.

The table will create and it is going to provide us with some flexibility in the calculation procedure.
Now, in cell D6, write down the following formula to get the Home Location. To get the value the LEFT and FIND functions will help us.

=LEFT([@[Unformatted Addresses]],FIND(",",[@[Unformatted Addresses]])-1)

Press Enter and you will see all the cells of the corresponding columns will get the formula. You don’t need to use the Fill Handle icon anymore.

Breakdown of the Formula

We are breaking down our formula for cell D6.

FIND(“,”,[@[Unformatted Addresses]]): This function returns 00018.

LEFT([@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]])-1): This formula returns 1975 Bathgate Ave.

To get the Area, write down the following formula in cell E6. We will use the MID and FIND functions to get the value.

=MID([@[Unformatted Addresses]],FIND(",",[@[Unformatted Addresses]])+1,FIND(",",[@[Unformatted Addresses]],FIND(",",[@[Unformatted Addresses]],FIND(",",[@[Unformatted Addresses]])+1)+1)-FIND(",",[@[Unformatted Addresses]])-5)

Press Enter.

Breakdown of the Formula

We are breaking down our formula for cell E6.

FIND(“,”,[@[Unformatted Addresses]]): This function returns 00018.

FIND(“,”,[@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]])+1): This formula returns 00025.

FIND(“,”,[@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]])+1)+1): This formula returns 00029.

MID([@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]])+1,FIND(“,”,[@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]],FIND(“,”,[@[Unformatted Addresses]])+1)+1)-FIND(“,”,[@[Unformatted Addresses]])-5): This formula returns Bronx.

In cell F6, write down the following formula for State. We are going to use the MID and FIND functions. Our formula will also contain the LEN function.

=MID([@[Unformatted Addresses]], FIND([@Area],[@[Unformatted Addresses]]) +LEN([@Area])+2,2)

Press Enter to get the value.

Breakdown of the Formula

We are breaking down our formula for cell F6.

LEN([@Area]): This function returns 00006.

FIND([@Area],[@[Unformatted Addresses]]): This formula returns 00019.

MID([@[Unformatted Addresses]], FIND([@Area],[@[Unformatted Addresses]]) +LEN([@Area])+2,2): This formula returns NY.

Write down the following formula using the MID and FIND functions in cell G6 to get the ZIP Code.

=MID([@[Unformatted Addresses]],FIND([@State],[@[Unformatted Addresses]])+3,8)

Press Enter.

Breakdown of the Formula

We are breaking down our formula for cell G6.

FIND([@State],[@[Unformatted Addresses]]): This function returns 00006.

MID([@[Unformatted Addresses]],FIND([@State],[@[Unformatted Addresses]])+3,8): This formula returns 10457.

Get all the addresses according to our desired format.

Method 3 – Applying Text to Columns Command

Steps:

Select the range of cells C5:C14.
In the Data tab, select the Text to Column command from the Data Tools group.

The Convert Text to Column Wizard will appear.
Choose the Delimited option and click Next.

In the 2nd step, choose Comma as the Delimiters and click Next.

Select General in the Column data format section.
Change the Destination cell reference from C6 to D6.
Click Finish.

We created a layout for the formatted addresses above row 6, Excel may give you a warning message, the image shown below. Ignore that and click OK.

You will get all the entities in your desired format.

4. Use of Flash Fill Feature

Steps:

Select cell C6.
In the Formula Bar, select the text up to the first comma with your mouse.
Press the ‘Ctrl+Shift+Right Arrow’ to select the text.
Press ‘Ctrl+C’ to copy the text.

Select cell D6 and press ‘Ctrl+V’ to paste the text.

Double-click on the Fill Handle icon to copy the formula up to cell D14.
Click on the drop-down arrow of the Auto Fill Options icon at the bottom of the Fill Handle icon.

Choose the Flash Fill option.

Every cell up to cell D14 extracted the 1st part of the addresses.
Follow the same process for columns titled Area, State, and ZIP Code.
Get all the addresses in a proper format.