Function Problems

How to Combine Address Fields in Excel with Commas and No Blanks

Camila Milosovich Oct 14, 2026 868 views

Question details

The user wants to merge multiple address components into a single cell, separated by commas, without leaving extra commas when some address fields are blank.

Combine Address Fields in Excel with Commas and No Blanks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating customer data and address fields for reporting, mailing labels, or data cleanup.
Observed behavior: When concatenating address fields, standard formulas often leave multiple unwanted commas next to each other if one or more cells in the range are blank.

Before you start

Verify which version of Excel you are currently using, as the most efficient solution requires the TEXTJOIN function, which is only available in Office 2019, Office 2021, Microsoft 365, or modern spreadsheet tools like WPS Office.

Solution 1Recommended

Use the TEXTJOIN Function (Modern Excel Versions)

The easiest and cleanest way to combine text while automatically skipping empty cells, available in Microsoft 365, Office 2019, and Office 2021.

The TEXTJOIN function is specifically designed to combine data from multiple ranges or strings, allowing you to specify a delimiter (like a comma) and choose whether to ignore empty cells.

Select the destination cell

Click on the cell where you want the combined, formatted address to appear.

Enter the TEXTJOIN formula

Type the formula =TEXTJOIN(", ", TRUE, F1:F6) into the cell or formula bar. Adjust the range F1:F6 to match the exact cells containing your address components.

Apply the formula

Press Enter. The TRUE argument ensures that any blank fields in the range are completely ignored, preventing unwanted double commas.

Use the TEXTJOIN Function (Modern Excel Versions)

Pro Tip: You can change the delimiter from a comma to a space, a hyphen, or a new line (using CHAR(10)) by modifying the first argument in the TEXTJOIN formula.

Solution 2

Use Concatenation with IF Functions (Excel 2016 and Older)

A reliable workaround using standard operators for older Excel versions that do not support the TEXTJOIN function.

Advanced Spreadsheet Functions

Combine Address Fields Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides full built-in support for advanced functions like TEXTJOIN, allowing you to merge cells, organize mailing lists, and manage complex datasets seamlessly without worrying about version compatibility.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your address fields.
2. Input the TEXTJOIN formula: Click on an empty cell and enter =TEXTJOIN(", ", TRUE, F1:F6) to combine the data.
3. Fill down the column: Press Enter, then click and drag the fill handle at the bottom right corner of the cell to apply the formula to all other addresses in your list.

Fully compatible with Microsoft Excel formulas, including TEXTJOIN and IFSeamlessly handles XLSX and CSV formats for customer data managementFree, lightweight, and features an intuitive, tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting extra commas when using the standard CONCATENATE function?

The CONCATENATE function (or the & operator alone) blindly merges exactly what you tell it to. If you hardcode a comma between cell references and one of those cells is empty, Excel will still output the comma, resulting in duplicates. Using TEXTJOIN with the TRUE argument solves this.

Can I use a space instead of a comma to combine address fields?

Yes. You can easily change the separator by modifying the first argument in the TEXTJOIN function. Simply change ", " to " " to separate your merged address fields with a single space.

What does the #NAME? error mean when I type the TEXTJOIN formula?

The #NAME? error indicates that your current version of Excel does not recognize the TEXTJOIN function. This happens in Excel 2016 and older versions. To fix this, you will need to use the alternative IF and ampersand (&) concatenation method instead.

Excel & Data Problems