Data Import & Export

Combine ZIP Codes by City and State in Excel

Camila MilosovichCamila Milosovich Nov 12, 2026 868 views

Question details

Combine ZIP codes that share the same city and state into a single pipe-separated value in Excel.

Combine Multiple ZIP Codes by City and State in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The worksheet has ZIP codes, cities, and states in separate columns, and the user needs one result row for each unique city-state combination.
Observed behavior
The desired result groups matching City and State values and joins their associated ZIP codes with the | character.
Before you start

Format the ZIP Code column as Text before entering or importing data so leading zeroes are preserved. Confirm that city and state names use consistent spelling and spacing, because Excel treats different text values as separate groups.

Solution 1Recommended

Use a Microsoft 365 dynamic array formula to group and join ZIP codes

Use this method in Microsoft 365 or Excel for the web when you want a live result that updates as the source ranges change.

This formula creates a unique City-State list, filters the ZIP codes for each pair, and joins the matching values with a pipe character. It assumes ZIP codes are in A2:A11, cities are in B2:B11, and states are in C2:C11.

1
Select the first result cell

Click an empty cell where the grouped results should begin, such as E2. Make sure the cells below and to the right are empty so the formula can spill its results.

2
Enter the grouping formula

Type or paste this formula into the selected cell: =LET(groups,UNIQUE(B2:C11),HSTACK(groups,BYROW(groups,LAMBDA(r,TEXTJOIN("|",TRUE,FILTER(A2:A11,(B2:B11=INDEX(r,1,1))*(C2:C11=INDEX(r,1,2)),""))))))

3
Adjust the source ranges

Replace A2:A11, B2:B11, and C2:C11 in the formula with the ZIP Code, City, and State ranges in your worksheet, then press Enter.

Use a Microsoft 365 dynamic array formula to group and join ZIP codes
One row per location: The spilled output shows City and State in the first two columns and all matching ZIP codes joined with | in the next column.
Group location data in a spreadsheet

Combine ZIP Codes by City and State with WPS Spreadsheets

WPS Spreadsheets lets you organize city and state data and use modern spreadsheet formulas to produce a compact, pipe-separated ZIP code list for each location group.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the ZIP Code, City, and State columns.
  2. 2. Preserve ZIP code formatting: Select the ZIP Code column, right-click, choose Format Cells, and set the category to Text if ZIP codes can begin with zero.
  3. 3. Choose an empty output area: Click the first empty cell for the grouped results, leaving enough blank cells below it for the formula output.
  4. 4. Enter the grouping formula: Enter a formula using UNIQUE, FILTER, and TEXTJOIN for your ZIP Code, City, and State ranges, then press Enter to return one row per city-state group.
Group matching City and State values into a single location record.Use TEXTJOIN, FILTER, UNIQUE, and dynamic-array workflows for ZIP code summaries.Open and edit Microsoft Excel .xlsx and .xls files with format compatibility.Use a familiar spreadsheet interface for sorting, filtering, and preparing imported data.
microsoft office alternative - wps office

Frequently Asked Questions

How do I keep leading zeroes in ZIP codes when grouping data?

Format the ZIP Code source column as Text before importing or entering values. If the ZIP codes are already numeric, use TEXT with an appropriate number of digits, such as TEXT(A2,"00000"), before combining them.

Why does the formula return separate rows for the same city and state?

Check for inconsistent text, such as trailing spaces, different abbreviations, or different capitalization. Use TRIM to remove extra spaces and standardize city and state values before applying UNIQUE.

Can I use a different separator instead of a pipe character?

Yes. In the TEXTJOIN formula, replace "|" with the separator you want, such as ", " for commas or CHAR(10) for line breaks. In Power Query, replace "|" in Text.Combine with your preferred delimiter.

Does the Microsoft 365 formula work in older Excel versions?

The formula requires dynamic-array functions such as LET, UNIQUE, FILTER, HSTACK, and BYROW. In older Excel versions, use Power Query or helper columns and a PivotTable-based workflow instead.

How can I refresh grouped ZIP codes after new rows are added?

For Power Query, add the new records to the source Excel table and select Data > Refresh All. For a formula, use Excel Table column references or expand the referenced ranges so new records are included.