### 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.

**Download Practice Workbook**

Download this practice workbook while you are reading this article.

## Related Articles

**<< Go Back to Address Format | Text Formatting | Learn Excel**

Get FREE Advanced Excel Exercises with Solutions!