When we copy some addresses from a different file, it is stored in a single cell. As a result, we need to format them in the perfect order for the convenience of other users. We can easily format addresses in several ways using Excel. If you are curious to familiarize yourself with those approaches, download our practice workbook and follow us.

## Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

## 4 Easy Ways to Format Addresses in Excel

To demonstrate the approaches, we consider a dataset of **10** employees of an organization and their residency. The **names** of the employees’ are in column **B**, and their **addresses** are in column **C**.

### 1. Using FIND, LEFT, MID and RIGHT Functions

In this method, we will use the **FIND**, **LEFT**, **MID**, **RIGHT**, and **LEN** functions to format addresses in Excel. The procedure is given as follows:

**📌 Steps:**

- At the beginning of this process, select cell
**D6**. - Then, write down the following formula into the cell.

`=FIND(",",C6)`

- Press
**Enter**.

- After that, select cell
**E6**and write down the following formula in the cell.

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

- Again, press the
**Enter**.

- Similarly, 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.

- Now, to 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)`

- Then, press
**Enter**to get the**Home Location**.

- Next, 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**.

- After that, 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 the
**Enter**key to get the value.

- At last, in cell
**J6**, write down the following formula to get the value of the**ZIP Code**.

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

- Similarly, press
**Enter**to get the value.

- Now, select the range of cells
**D6:J6**. **Double-click**on the**Fill Handle**icon to copy all the formulas up to row**14**.

- Finally, you will get all of the unformatted addresses in the correct format.

At last, we can say that all of our formulas worked successfully, and we are able to format addresses in Excel.

**🔍 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**.

**Read More: ****How to Separate Address in Excel with Comma (3 Easy Methods)**

### 2. Applying Combined Formula to Format Addresses

In this process, we are going to use several combined formulas to format our addresses. The procedure is explained below:

**📌 Steps:**

- At first, select the range of cells
**B5:J15**.

- Then, to convert the data range into a table, press
**‘Ctrl+T’**. - As a result, a small dialog box called
**Create Table**will appear. - Then, 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. As a result, we 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**.

- Then, to get the
**Area**, write down the following formula in cell**E6**. Here, 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)`

- Again, 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.**

- After that, in cell
**F6**, write down the following formula for**State**. For that, we are going to use the**MID**and**FIND**functions. Here, our formula will also contain**the LEN function**.

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

- Similarly, 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**.

- At last, 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**for the last time.

**🔍 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**.

- You will get all the addresses according to our desired format.

In the end, we can say that all of our formulas worked perfectly, and we are able to format addresses in Excel.

**Read More: ****How to Separate Address Number from Street Name in Excel (6 Ways)**

**Similar Readings**

**Formula to Create Email Address in Excel (2 Suitable Examples)****How to Split Inconsistent Address in Excel (2 Effective Ways)****Create Email Address with First Initial and Last Name Using Excel Formula**

### 3. Applying Text to Columns Command

In this approach, we will use Excel’s built-in **Text to Columns** command to format addresses. The steps of this procedure are given below:

**📌 Steps:**

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

- As a result, the
**Convert Text to Column Wizard**will appear. - Then, choose the
**Delimited**option and click on**Next**.

- Next, in the
**2nd**step, choose**Comma**as the**Delimiters**and click**Next**.

- In the last step, select
**General**in the**Column data format**section. - After that, change the
**Destination**cell reference from**C6**to**D6**. - Finally, click
**Finish**.

- As we have created a layout for the formatted addresses above
**row 6**, Excel may give you a warning message, like the image shown below. Ignore that and click**OK**.

- You will get all the entities in your desired format.

Thus, we can say that our method worked successfully, and we are able to format addresses in Excel.

**Read More: ****How to Separate City State and Zip from Address Using Excel Formula**

### 4. Use of Flash Fill Feature

In this method, we will use the **Flash Fill** feature of Excel to format addresses according to our desire. The steps of this method are given below:

**📌 Steps:**

- First of all, select cell
**C6**. - Now, in the
**Formula Bar**, select the text up to the first**comma**with your mouse. - Besides it, you can also press the
**‘Ctrl+Shift+Right Arrow’**to select the text. - Press
**‘Ctrl+C’**to copy the text.

- Now, 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**.- After that, click on the
**drop-down arrow**of the**Auto Fill Options**icon at the bottom of the**Fill Handle**icon.

- Then, choose the
**Flash Fill**option.

- You will see that every cell up to cell
**D14**extracted the**1st**part of the addresses. - Similarly, follow the same process for columns titled
**Area**,**State**, and**ZIP Code**. - You will get all the addresses in a proper format.

Finally, we can say that our method worked perfectly, and we can format addresses in Excel.

**Read More: ****How to Make an Address Book in Excel (An Ultimate Guide)**

## Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to format addresses in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website **ExcelDemy** for several Excel-related problems and solutions. Keep learning new methods and keep growing!