Excel Address Format (All Things You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, you will learn how to format addresses in Excel. You will find four different techniques by which you will be able to format your address in Excel. In addition to that, you will learn how to create a mailing address from a given address.

We have used Microsoft 365 to prepare this article. But you can use these methods in Excel versions from Excel 2003 onwards.

Generally, we use address formatting in Excel to present data in a consistent manner. It helps us to split addresses into different components or combine components into one. Formatting addresses makes the comparison or sorting of addresses easier by ensuring consistency. Above all, it helps reduce data entry work and improves the accuracy of the data. The easiest of all these methods is using the Flash Fill feature. If you are comfortable with using functions then you have various options in your hand to format address. A lot of functions are available in Excel to help you format your address.

Final Output of Applying the Flash Fill Feature

Download Practice Workbook

Overview of Standard Address Format

While formatting addresses in Excel, we need to follow some standard rules. For example, house and building numbers should be in the first line. City and state numbers will appear in the next part of the address. In the standard format, the zip code appears in the last part of the addresses. You should pay special attention while working with the Zip code. Some of the zip codes start with “0”. If you don’t format the column as Zip Code, it will remove the leading zero.

Zip Code Without Formatting

Click to see full image

In the image above, you will find that Excel has automatically removed the leading zero. So, we will format the Zip Code column to solve the issue.

  • Select the column and press Ctrl+1
  • In the Format Cells dialog box, choose options like the image below.

Formatting Column as Zip Code

After that, you will find that Excel is not removing the leading zero anymore.

Zip Code After Proper Formatting

Click to see full image

Note: Though it is not mandatory, it is a good practice to format the “Street” and “Town” columns as Text and “Contact No.” column as Phone Number

Read More: Organize Addresses in Excel

How to Format Address in Excel

1. Use Excel Features

1.1  Applying Text to Columns Command

To format address using the Text to Columns wizard

  • Select the data in Column B (B5:B10). Click on the Data tab>>Data Tools>>Text to Columns wizard.
Selecting Text to Columns Wizard

Click to see full image

After selecting the wizard, you will find 3 dialog boxes that will appear one by one. Choose the following options in those dialog boxes.

  • Delimited in the first dialog box.

First Dialog Box in Text to Columns Wizard

  • If you look at the address in our dataset, you will find that different parts are separated by commas. So, choose Comma in the delimiters group.

Selecting Delimiters to Format Address

  • In the last one, you needn’t do anything. Just click on Finish.

Final Step to Perform Text to Coumns Wizard

The output is similar to the following image.

Output of Text to Columns Wizard

1.2 Use of Flash Fill Feature

  • First, select the cell that contains the address. Then, copy up to the first comma (the street part of the address) and paste it into the D5
Copy Address to Apply Flash Fill

Click to see the full view of image

  • After that, double-click on Fill Handle to copy the formula up to cell D10.
Excel Uses Fill Series to Fill the cells

Click to see the full view of image

Excel fills the blank cells with the Fill Series option. However, we will change this to the Flash Fill option in order to get the desired output.

Altering Fill series with Flash Fill

Click to see the full view of image

So the output image will be as follows.

Apply Flash Fill and Get Output

Click to see the full view of image

Note: In the case of city and zip code, use the same method. Just change the value you copied in the first step.
Final Output of Applying the Flash Fill Feature

Click to see full image

The final output will be similar to the following image.

Flash Fill Shortcut: The shortcut for Flash Fill in Windows is Ctrl+E while it is Command+E for Mac users.

When to Use Excel Features or Non-Formula Methods to Format Address?
Using Excel features is quick and easy. They are especially useful in the following cases.

  • If you import data from a website or reporting system.
  • When you are not that much expert at formulas.
  • Scenarios where you don’t want to change the main address.

On the other hand, if you use features to format addresses, data may not update automatically.

Read More: Format Addresses in Excel

2. Use Excel Formulas to Format Address

2.1 Using FIND, LEFT, MID, and RIGHT Functions

Step 1: Finding Separator Positions

In this method, we will use FIND, LEFT, MID, and RIGHT functions. We need to define the starting and end positions in the arguments of these functions. For example, to get the street portion of the address, we need to mention the position of the first comma in the argument of the LEFT function. In the same way, the position of the second and third comma is necessary to get the Town name of the address.

1st Separator: Apply the following formula in the cell. We will apply this in cell D5.


FIND(“,”,C5): Here, the searching criteria is a comma and the text where we will perform searching is cell C5. We will get the position of the first comma.

Finding First Separator

Click to see full image

2nd Separator: The FIND function returns the position of the first appearance of any character. To get the second one, in this case, the search will have to start from the next position of the first comma, which is D5+1. 


3rd Separator: Modify the formula to find the 3rd separator. Replace the D5+1 argument with E5+1. You will get the position of 3rd separator.


So, you’ll get 1st, 2nd and 3rd seperators.

1st,2nd & 3rd Separator in the Address

Click to see full image

Step 2: Insert Street Address Formula

We will separate street addresses using the LEFT function. Apply the following formula to cell G5 and hit ENTER. Use AutoFill to fill up the remaining cells.

=LEFT(C5, D5-1) 

The LEFT function extracts a part of a text according to the position we define. In this case, we defined the position before the comma. So, the formula will extract all the things that it finds before that comma.

Separating Street Address

Click to see full image

Step 3: Separating Town Name

We will use the MID function to find out the Town address from a given address. Just put the following formula in the cell and get the result.

=MID(C5, D5+1,E5-D5-1)

🔎 Formula Breakdown

  • C5: The first argument of the MID It is the text from which the function will extract some characters.
  • D5+1: Defines where the MID function starts to extract characters.
  • E5-D5-1: Indicates the end of extraction of MID

The output will look like the following one.

Town Address by MID Function

Click to see full image

Step 4: Separating Zip Code

Write the formula in I5 cell to separate Zip.


🔎 Formula Breakdown

  • LEN(C5): Finds the length of the text C5.
  • LEN(C5)-F5: Subtracts the position of the 3rd separator from the length of C5 to get how many characters the RIGHT function would extract.
  • RIGHT(C5,LEN(C5)-F5): Extracts all the characters that are present after the 3rd comma.
Formula for Zip Address

Click to see full image

2.2 Using a Combined Formula When Delimiters Are Not Same 

  • First, convert the range into a table by pressing Ctrl+T
  • Apply the following formula in the cell C5.

You will get the street address in Column C.

Finding Street Address From Table

Click to see full image

🔎 Formula Breakdown

  • FIND(“;”,[@Address]): Returns an array that contains the position of the delimiter “;” in the address.
  • LEFT([@Address],FIND(“;”,[@Address])-1): Extracts the values that appear before the delimiter “;”.

Getting Other Values:

In order to get the town names, use the following formula in your worksheet. We will apply this in the cell D5.

=MID([@Address], FIND(";", [@Address]) + 2, FIND("""", [@Address], FIND(";", [@Address]) + 2) - (FIND(";", [@Address]) + 2))

To get the Zip code, we will use the following formula in the cell E5.

=MID([@Address], FIND(":", [@Address]) + 1, 6)

The final output will be similar to the following image.

Final Output when Delimiters are Not Same

Click to see full image

2.3 Using TEXTBEFORE and TEXTAFTER Functions

  • Firstly, apply the following formula at cell D5 and hit ENTER, use the AutoFill feature to fill up the remaining cells.

Click to see full image

  • It’s now the turn of Town. We will apply the following formula at E5 and repeat the same procedure of finding Street.

Here, TEXTAFTER(C5,”,”,1) finds the text after the first comma in C5, which is Townsville, TX, 98765. Then, the TEXTBEFORE function takes Townsville, TX, 98765 as its first argument and finds the content before the first comma in it; which is Townsville.

The output looks like the following image.

Finding out Town by TEXTBEFORE and TEXTAFTER

Click to see full image

Finally, we will find the Zip code in the Zip column. Apply the formula at F5 and use AutoFill like before.

Applying Formula to Find Zip of Address

Click to see full image

Note: Both TEXTBEFORE and TEXTAFTER functions are available only in Microsoft 365. So, you should have a subscription to use them.

2.4 Using TEXTSPLIT and CHOOSECOLS Functions

  • Write down the formula in the first cell under the Street
The TEXTSPLIT function generates an array based on the given criteria. In this case, the delimiter is a comma. So the array is:{456 Elm Avenue, Townsville,Tx,98765}. The CHOOSECOLS function takes the first value of the array and puts it in the cell.
Street Address by TEXTSPLIT and CHOOSECOLS

Click to see full image

In order to find the Town, we will modify the formula and put 2 in place of 1 in the argument of the CHOOSECOLS function. As a result, now we will get the second value in the array, which is “Townsville”.

Finding the Town Using TEXTSPLIT and CHOOSECOLS

Click to see full image

Finally, we will find the Zip code by changing the last argument of the CHOOSECOLS function to 4. The output will be similar to the following image.

CHOOSECOLS and TEXTSPLIT to Find the Zip Code

Click to see full image

Note: TEXTSPLIT and CHOOSECOLS functions are available only in Microsoft 365. So, you should have a subscription to use them.

When to Use Formula Method to Format Address in Excel

Formatting addresses using formulas is beneficial when we need dynamic and automated data manipulation. These methods are:

  • Flexible &
  • Can handle complex transformations.

Some negative sides of using formulas are:

  • Requires sound knowledge of formulas and
  • Slower for large datasets

Read More: Make an Address Book

3. Using Power Query to Format a One-Column Address List

We have a column that contains the names and addresses of some employees in a company. We will convert it to readable table format.

70-One Column Address List

  • Convert the list into a table by pressing Ctrl+T.
  • Open the table in Power Query
  • In the Power Query editor, add an Index Column from the Add column option.

71- Adding Index Column

The output of adding the Index Column:

Index Column Added

In the list, you will find that each employer’s address expands to consecutive 6 rows (Including name). If we assign numbers in such a way that will start from 0 for the first data of each employee and then go to the last data entry of that employee and again start from 0 for another employee, it will be a good technique.

  • Select Standard >>Modulo option.

Adding Modulo Columns

  • A dialog box named Modulo will appear where we will input value “6” since each employee data expands to consecutive 6 rows.

Modulo Dialog Box

The output of adding a Modulo column looks like the following image.

Modulo Column Added in Power Query Editor

  • Now, we will transform address data into columns. Select the options like the following image in your dataset.

Creating Columns from Address Data

You will get an output like the following image.

New Columns in Power Query Editor

Click to see full image

  • Holding down the Shift key, select Column 1 to Column 5.
  • From the Transform tab, select Fill >> Up.

Fill Up from the Transform Tab

The output of this step is similar to the following image.

Output of Applying the Fill Up Option

Click to see full image

  • After that, we will click on the drop-down arrow of Column 0 and deselect the option “Null”.

Removing Null from the Dataset

  • Delete the Index Give proper names to the columns like the following image
Giving Names to the Columns

Click to see full image

Finally, click on Close & Load to see the results in your worksheet.

Address Loaded on Worksheet

Read More: Formula to Create Email Address

How to Format Full Address in One Cell with Line Break in Excel

In order to format the full address in one line, we will use the line break instead of the Wrap Text option. We have a dataset in Excel like the following image. We want to increase the readability of the addresses.

Dataset for Applying Line Break

  • First, we will try the Wrap Text The output looks like the following image. (You will have to adjust the row height to make the address readable. We set it to “30”)

Address After Applying the Wrap Text Option

Looking at the image above, you will decide that the address is not correctly formatted since you cannot differentiate different parts of it. To solve this, we will apply line break.

Process Description

  • Click on cell B4 and place your cursor right after the first comma after the name “Sophia Anderson”
  • Delete the comma by pressing Backspace. Press Alt+Enter together to insert new line.
  • In the same way, place your cursor after the 3rd comma, delete it and insert new line.
  • Repeat the whole process for all the cells and finally change the Row Height to 45.

The output is similar to the following image.

Final Output of Applying the Line Break

Read More: Excel Formula to Create Email Address with First Initial and Last Name

How to Format Mac Addresses in Cells by Adding Colon Symbol in Excel

We have the following dataset in Excel. We will format them as Mac addresses.

Dataset to Format Mac Addresses

  • Apply the following formula in C5 and hit ENTER to get the result.
  • Use the Fill Handle feature to get addresses in the other cells as well.

The output is similar to the following image.

Applying Formula to get the Mac Address

🔎 Formula Breakdown

  • SEQUENCE(6,1,1,2) = {1,3,5,7,9}: Generates an array of 6 numbers starting from 1 and an increment of 2.
  • MID(B5,SEQUENCE(6,1,1,2),2) = {00, 1A, 2B, 3C, 4D, 5E}: It also generates an array that extracts consecutive 2 characters from B5 according to the array generated by the SEQUENCE function.
  • TEXTJOIN(“:”,1,MID(B5,SEQUENCE(6,1,1,2),2)) = 00:1A:2B:3C:4D:5E: Joins the array elements generated by the MID function with a colon between them.

How to Find Mailing Address from Name and Address?

In Excel, we can combine the name and address of a customer to find out the mailing address. We will use the SUBSTITUTE function to do that. Our dataset looks like the following image.

Dataset to Find Mailing Address

  • At this point, we will apply the following formula in cell D5, hit Enter, and apply Wrap Text to the cell.
=SUBSTITUTE(B5&CHAR(10)&C5,", ",CHAR(10),1)

Formula to Find Mailing Address

  • After dragging the Fill Handle, the output will be as follows.

Output of SUBSTITUTE Function

🔎 Formula Breakdown

  • “,”&CHAR(10) : Char(10) is the character that adds a new line in Excel. So, this part adds a comma and then adds a new line to the cell.
  • B5&CHAR(10)&C5 : Concatenates the content of B5 with C5 and adds the C5 in a new line. 
  • SUBSTITUTE(B5&CHAR(10)&C5,”, “,CHAR(10),1) : Replaces the first comma in the combined text of B5, CHAR(10), and C5 with CHAR(10). In other words, it replaces the first comma with a new line character.

Read More: Format a Column for Email Addresses

Things to Remember

  • First of all, identify the specific components of the address you need to work with, such as street, city, postal code, country, or any other relevant information.
  • Use consistent formatting across all address entries in your dataset. It will help you analyze the address.
  • Since Excel automatically removes leading zero, pay attention to leading zeros in postal codes or other numerical components of an address.

Frequently Asked Questions

1. What are some common challenges in address formatting?

Answer: Some of the most common challenges in address formatting are: inconsistent formats, missing components, leading zeros, and data entry errors.

2. Can I combine separate address components into a single cell in Excel?

Answer: You can use some built-in functions of Excel to combine separate addresses into a single cell. You may use CONCATENATE or TEXTJOIN functions. Also, you may use the Ampersand operator or custom formula to do that.

3. Can I import or export address data with specific formatting requirements in Excel?

Answer: You can use CSV, .txt, or VBA macros to import and export data with specific formatting requirements.


Hey! You have reached the end of the article on Excel address format. The easiest way to format address is to use the Flash Fill feature. Also, you can use the FIND, MID, or LEFT functions to format addresses. Moreover, if you have Microsoft 365 subscription, you may use the TEXTBEFORE and TEXTAFTER functions. Hopefully, you enjoyed all the items covered in the article and you will apply these methods in your worksheet to format your address. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries. Finally, please visit our site for more exciting articles on Excel.

Excel Address Format: Knowledge Hub

<< Go Back to Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF