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 1^{st} 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 2^{nd} Separator

– To add the second Separator, type in the formula.

– Syntax of the formula:

**=FIND(“,”,Cell_with_Address, 1 ^{st}_separator + 1)**

– In our case the formula will be

**=FIND(“,”,A2,B2+1)**

### Step 3 – Add the 3^{rd} Separator

– To add the third Separator, type in the formula.

– Syntax of the formula:

**=FIND(“,”,Cell_with_Address, 2 ^{nd}_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,1 ^{st}Separator – 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,1 ^{st}Separator + 1,3^{rd}Separator – 2^{nd}Separator – 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,2 ^{nd}_Separator + 1,3^{rd}_Separator – 2^{nd}_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) – 3 ^{rd}_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.