How to Format Addresses in Excel (4 Easy Methods)

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.

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

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

  • Again, press the Enter.

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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

Using FIND, LEFT, MID, and RIGHT Functions to Format Addresses

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)

Applying Combined Formula to Format Addresses

  • 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)

Applying Combined Formula to Format Addresses

  • 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)

Applying Combined Formula to Format Addresses

  • Similarly, press Enter to get the value.

Applying Combined Formula to Format Addresses

πŸ” 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)

Applying Combined Formula to Format Addresses

  • Press Enter for the last time.

Applying Combined Formula to Format Addresses

πŸ” 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


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.

Applying Text to Columns Command to Format Addresses

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

Applying Text to Columns Command to Format Addresses

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

Applying Text to Columns Command to Format Addresses

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

Applying Text to Columns Command to Format Addresses

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.

Use of Flash Fill Feature to Format Addresses

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

Use of Flash Fill Feature to Format Addresses

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

Use of Flash Fill Feature to Format Addresses

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!


Related Articles

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo