To combine an address, city, state, and ZIP code in Excel, put them in separate cells and join them with a formula. If A2 contains the street, B2 the city, C2 the state, and D2 the ZIP code, enter =A2&", "&B2&", "&C2&" "&D2 in E2; it returns a single line such as 123 Main St, Boston, MA 02108.
Set up the address columns
Keep each part of the address in its own column before combining anything. A typical layout has street address in column A, city in B, state or province in C, and ZIP or postal code in D. Put a heading above each column so the source data remains understandable, and use the next column for the combined result. For example, label E1 “Mailing address” and enter the formula in E2.
The formula joins cell contents with text separators. The ampersand (&) joins one item to the next, while text inside quotation marks supplies punctuation and spaces. In the example, the comma and space after A2 separate the street from the city; another comma separates the city and state; and a space separates the state from the ZIP. Excel calculates the result as text, leaving the original four columns unchanged.
This layout works for a simple U.S. mailing address. If your source has apartment or suite, building, country, or a second address line, decide where those parts belong before writing the formula. For instance, you might put an apartment number in its own column and include it between the street and city. The right order and punctuation depend on the postal format you need; not every country puts the postal code after the state or province.
Choose a formula for your data
The ampersand formula is easy to read and works across Excel versions. With the same A2:D2 layout, the formula shown above assumes every field is filled in. If some rows may have empty fields, fixed punctuation can leave extra commas or spaces. Excel for Microsoft 365 also offers TEXTJOIN, which can skip empty items. For a more controlled result that puts commas between address, city, and state, then a space before the ZIP, use the following formula.
With street in A2, city in B2, state in C2, and ZIP in D2, enter this in E2: =TEXTJOIN("",TRUE,A2,IF(AND(A2<>"",B2<>""),", ",""),B2,IF(AND(B2<>"",C2<>""),", ",""),C2,IF(OR(A2<>"",B2<>"",C2<>"")," ",""),D2). If A2 is 123 Main St, B2 is Boston, C2 is MA, and D2 is 02108, the result is 123 Main St, Boston, MA 02108. If B2 is empty but the other fields contain data, the formula avoids inserting an empty city followed by a doubled comma.
TEXTJOIN is available in Microsoft 365 and Excel 2019 or later. In the formula, the first argument is the delimiter, set to an empty string because the formula supplies its own separators. TRUE tells Excel to ignore empty items. The IF expressions add punctuation only when the relevant cells contain values. If your Excel installation uses semicolons as formula argument separators because of regional settings, replace the argument commas with semicolons when entering the formula; the examples here use standard comma separators.
Protect leading zeros in ZIP codes
ZIP codes are identifiers, not quantities, so a leading zero matters. For example, a five-digit code such as 02108 can be changed to 2108 if Excel interprets it as a number. Once that zero is removed, ordinary joining cannot put it back reliably unless you know the required ZIP length and the original value.
- Best for new data: Set the ZIP column to Text before entering or importing the codes. Select the column, choose Home > Number Format menu > Text, then enter the values. Check imported data afterward, because the import method can affect how Excel interprets it.
- If the zero is already missing: Re-import or correct the value from a reliable source when possible. A five-digit number can be displayed with a leading zero using a custom number format, but the underlying value remains numeric.
- If all codes are known to be five digits: With the ZIP value in D2 as a number and the other address parts in A2:C2, use
=A2&", "&B2&", "&C2&" "&TEXT(D2,"00000"). For the numeric value 2108, TEXT returns 02108, so the complete result is123 Main St, Boston, MA 02108.
The TEXT formula is suitable only when the format is known. U.S. ZIP+4 codes need a different format and may be stored as text; international postal codes can contain letters, spaces, or varying lengths. Do not apply a five-digit mask to codes that do not follow that pattern. Also, changing a cell’s number format alone does not guarantee that a concatenated result preserves the displayed appearance: the formula works with the cell’s value, so explicitly format numeric ZIP values with TEXT when needed.
Handle blank fields and inconsistent entries
Blank cells are a common reason a combined address looks untidy. The simple ampersand formula always inserts its commas, even if one of the cells is empty. The TEXTJOIN example above handles several missing fields more gracefully, but it cannot determine whether a value is correct or whether a missing piece should be supplied. Review unusual rows rather than assuming a formula can repair incomplete source data.
Extra spaces in imported fields can also create results such as 123 Main St , Boston, MA 02108. If the source columns contain accidental leading or trailing spaces, clean those columns or wrap individual references in TRIM. With street, city, state, and ZIP in A2:D2, this version trims each value: =TRIM(A2)&", "&TRIM(B2)&", "&TRIM(C2)&" "&TRIM(D2). For the same clean example values, it returns 123 Main St, Boston, MA 02108. TRIM removes ordinary extra spaces at the beginning and end and reduces repeated internal spaces; it does not correct misspellings, inconsistent abbreviations, or every kind of invisible character that may arrive in copied data.
Decide on conventions before combining a large list. One file may use “Street” while another uses “St,” or state names may be spelled out in some rows and abbreviated in others. Combining fields preserves those differences. If the list will be mailed or uploaded to another service, check its required address format and validate the underlying addresses separately.
Fill the formula down the list
After confirming the result in E2, copy the formula to the other rows. In Excel for Microsoft 365 on Windows, select E2 and drag the fill handle—the small square at the lower-right corner of the selection—down the column. You can also select E2 and the cells below it, then choose Home > Fill > Down, or press Ctrl+D. Excel adjusts relative references automatically: the formula copied to E3 refers to A3, B3, C3, and D3.
If the source data is an Excel table, entering the formula in the first row of the new table column will generally fill that calculated column for the table’s rows. Tables also make it easier to keep headings attached to data as rows are added. On a Mac, the same formula works, and the ribbon’s Home tab includes fill commands; keyboard shortcuts can differ according to keyboard and Excel settings. Excel for the web also supports these formulas, although the ribbon layout may vary slightly.
When you need to use the combined addresses outside the workbook, copy the result column and use Paste Special > Values to replace formulas with their current results. This is useful when sending a static list or preparing a file for an application that expects plain address text. Keep the original component columns if you may need to correct a city or ZIP later; a single combined string is harder to edit and split back into reliable fields.
Troubleshoot formula results
- The formula appears as text: The destination cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, edit the cell, and press Enter.
- A comma or space is missing: Check the text separators inside quotation marks and confirm the formula points to the intended columns. A comma is part of the formula’s output only when it is included as quoted text.
- The ZIP loses its first zero: Check whether the source value is numeric and whether the formula needs TEXT with a suitable format mask. If the original zero was discarded and the code length is uncertain, recover it from the source rather than guessing.
- The result has an unexpected space: Look for spaces already present in the source cells. TRIM can help with ordinary extra spaces, but inspect imported text if cleanup does not solve it.
- Excel rejects the formula: Verify that the quotation marks and parentheses are balanced and that your regional settings do not require semicolons between function arguments.
For a quick check, compare the combined value with the four source cells on several rows, including one with a leading-zero ZIP and one with a blank field. Testing only a fully populated, well-formatted row can miss the exact cases most likely to cause trouble later.
Formula results versus a manually combined list
A formula is usually the better choice when addresses may change or when you have many rows: update a source cell and the combined result recalculates. It is also repeatable, so you can use the same pattern for new data. The trade-off is that the result depends on the source cells and formula. If you remove or move those cells, the formula may no longer produce the intended address.
A manually typed combined address is independent, but it is easy to introduce inconsistent punctuation and difficult to update across many rows. Pasting formula results as values is a useful middle ground: first use formulas to create consistent text, then paste values if a fixed output is required. For postal work, retaining separate address fields is often more useful than keeping only the combined line because individual parts can be searched, corrected, or exported separately.
Frequently Asked Questions
How do I combine address fields with a comma in Excel?
With street in A2, city in B2, and state in C2, use =A2&", "&B2&", "&C2. Add the ZIP reference and a space if you want the full address, for example =A2&", "&B2&", "&C2&" "&D2.
How do I combine city, state, and ZIP without an extra comma before the ZIP?
With city in B2, state in C2, and ZIP in D2, use =B2&", "&C2&" "&D2. It produces a result like Boston, MA 02108, with a comma between city and state and a space before the ZIP.
Can I combine the fields without a formula?
Yes. You can type the address directly into one cell, but that does not update when the source fields change and can be inconsistent across many rows. A formula is easier to maintain; paste its results as values if you need a static list.
Why did Excel remove the zero at the start of my ZIP code?
Excel likely treated the ZIP as a number, and numbers do not retain leading zeros as part of their value. Store postal codes as text, or, when the code length is known and the value is numeric, use TEXT with an appropriate format such as 00000.
Will the formula work in Excel for Mac or Excel for the web?
The ampersand formula works in current Excel for Windows, Mac, and the web. TEXTJOIN is available in Microsoft 365 and supported newer Excel versions; menu placement and some keyboard shortcuts can differ, but the formula syntax is the same.
Related
- Can You Password Protect an Excel Spreadsheet?
- Can You Print an Excel Sheet? Settings, Pages, and Tips
- Can You Password Protect an Excel File? How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Header in Excel for Microsoft 365: Page SetupTo add a header in Excel for Microsoft 365 on Windows, open the worksheet, choose Insert > Text…
- How to Sort Dates in Google Sheets Without Mixing RowsTo sort dates in Google Sheets, select the full table, open Data > Sort range > Advanced range…
- Free Savings Tracker Google Sheets: Set Up a Simple SheetYes. You can make a free savings tracker in Google Sheets by starting with a blank spreadsheet, recording…
- Microsoft PowerPoint Loop Slideshow: Setup and FixesYes. In the PowerPoint desktop app, open Slide Show > Set Up Slide Show, select Loop continuously until…
- Best Monthly Budget Spreadsheet: 5 Options ComparedFor most people who want a straightforward monthly budget spreadsheet, start with the personal budget templates available in…
- How to Add Footnotes in WordTo add a footnote in Word for Microsoft 365 on Windows, place the cursor where the note marker…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.