On this page
01
What's the best formula to combine address columns in Excel?
TEXTJOIN is the best formula for combining address columns in Excel 2019, Excel 2021 and Microsoft 365. It puts a separator between each part and skips empty cells, so a row with a missing piece doesn't end up with a stray comma. Every example on this page uses this layout:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| Row 1 | Name | Street | City | State | ZIP |
| Row 2 | Brightwater Bakery | 88 Pleasant St | Brookline | MA | 02446 |
=TEXTJOIN(", ", TRUE, B2, C2, D2 & " " & E2)That returns 88 Pleasant St, Brookline, MA 02446. The state and ZIP are joined with a plain space first, because addresses are written MA 02446, not MA, 02446. The TRUE tells TEXTJOIN to ignore empty cells.
To copy the formula down, double-click the fill handle, the small square at the cell's bottom-right corner. Excel fills as far down as the column next to it has data.
02
How do you combine cells in older versions of Excel?
Use the ampersand (&), which joins text in every version of Excel and in every other spreadsheet app. Type each separator in quotes between the cells.
=B2 & ", " & C2 & ", " & D2 & " " & E2CONCATENATE does the same job with commas between the pieces, and still works everywhere. CONCAT, its newer replacement in Excel, also accepts ranges, but unlike TEXTJOIN it adds no separators, so you still type each one.
=CONCATENATE(B2, ", ", C2, ", ", D2, " ", E2)
=CONCAT(B2, ", ", C2, ", ", D2, " ", E2)Adding a unit only when there is one
Ampersand formulas don't skip blanks. If some rows have an apartment or suite in column F, wrap it in IF so an empty unit adds nothing. Rows with a unit become 88 Pleasant St Unit 3, Brookline, MA 02446; the rest are unchanged.
=B2 & IF(F2 = "", "", " " & F2) & ", " & C2 & ", " & D2 & " " & E203
Why did my ZIP codes lose their leading zero?
Excel treats a ZIP code like 02446 as the number 2446 and drops the zero. It happens to every ZIP that starts with 0, which covers New England, New Jersey and Puerto Rico. A map may still find Brookline, MA 2446 from the town name, but a four-digit ZIP doesn't help it, and on a row with no city it can fail outright. Other causes of misplaced pins are in why addresses land in the wrong place.
=TEXTJOIN(", ", TRUE, B2, C2, D2 & " " & TEXT(E2, "00000"))=IF(LEN(E2) > 5, TEXT(E2, "00000-0000"), TEXT(E2, "00000"))04
How do you clean up spaces and capitals before combining?
Run each part through TRIM. TRIM removes spaces at the start and end of a cell and squeezes double spaces down to one. They matter more than they look: 88 Pleasant St and 88 Pleasant St can make two copies of one address look different to a duplicate check.
=TEXTJOIN(", ", TRUE, TRIM(B2), TRIM(C2), TRIM(D2) & " " & TEXT(E2, "00000"))Text copied from a website or a PDF sometimes carries non-breaking spaces, which look like spaces but which TRIM leaves alone. Swap them for ordinary spaces first:
=TRIM(SUBSTITUTE(B2, CHAR(160), " "))PROPER turns 88 PLEASANT ST into 88 Pleasant St. It's cosmetic, since maps ignore capitals, and it has quirks: NW becomes Nw, MCALLISTER becomes Mcallister and 5TH AVE becomes 5Th Ave. Use it for printed lists and mail merges, not because a map needs it.
Before you combine
- ZIP column formatted as Text, or wrapped in
TEXT(E2,"00000"). - Every part trimmed of stray spaces.
- One style in the State column:
MAorMassachusetts, not both. - Subtotal rows, blank rows and repeated header rows deleted.
- Phone numbers and notes kept out of the address columns.
05
Can Excel combine addresses without a formula?
Yes, with Flash Fill. Type the first combined address by hand in the column next to your data, exactly as you want it, then press Ctrl+E or choose Data → Flash Fill. Excel spots the pattern and fills the rest of the column with plain text, not formulas.
- Check the odd rows. Flash Fill copies your example, so a row with no ZIP or an extra unit can come out wrong. Type a second example on a row that's different and run it again.
- It doesn't update. Change a street later and the combined column stays as it was. Formulas keep up; Flash Fill doesn't.
- Google Sheets has a cousin. Smart Fill offers to fill the column when it sees a pattern as you type.
06
How do you combine address columns in Google Sheets?
The same TEXTJOIN and ampersand formulas work in Google Sheets. Two differences matter: CONCAT in Sheets joins only two values, so use CONCATENATE or & instead, and ARRAYFORMULA can fill the whole column from a single cell.
=ARRAYFORMULA(IF(B2:B = "", "", B2:B & ", " & C2:C & ", " & D2:D & " " & E2:E))The array version uses ampersands on purpose: TEXTJOIN inside ARRAYFORMULA joins the entire column into one cell instead of working row by row. Sheets also has JOIN, which works like TEXTJOIN but doesn't skip empty cells.
07
How do you split a full address into separate columns?
To go the other way, from one Address column to Street, City, State and ZIP, use Data → Text to Columns in Excel: choose Delimited, tick Comma, and finish. Each comma-separated part lands in its own column.
- Insert empty columns to the right first. Text to Columns writes over whatever is next door.
- State and ZIP stay together (
MA 02446) because there's no comma between them. Run Text to Columns on that column again with Space as the delimiter, and in the last step set the ZIP column's format to Text so the zeros survive. - Rows with a unit after a comma (
88 Pleasant St, Unit 3, Brookline) come out one column longer than the rest. Filter for a filled-in extra column to find them.
=TEXTSPLIT(A2, ", ")=SPLIT(A2, ", ", FALSE)The FALSE matters in Sheets: without it, SPLIT breaks at every comma and every space, chopping the street into separate words. If the list is headed for the EZRoutePlanner Excel route planner you can skip both directions, because its import reads the address whether it's in one column or several. The full walk-through is in how to map addresses from Excel.