How to Combine Excel Cells Without Extra Commas Using TEXTJOIN
Question details
Combine data from multiple columns into a single cell while ignoring empty cells to prevent unwanted extra separators.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging data lists, addresses, or names across multiple columns where some columns might be entirely or partially blank.
- Observed behavior
- Standard concatenation methods generate trailing or consecutive commas when encountering empty cells, requiring a dynamic method to ignore blanks.
Ensure you are using Excel 2019, Microsoft 365, or a modern office suite like WPS Office, as older versions of Excel do not natively support the TEXTJOIN function.
Use the TEXTJOIN Function (Excel 2019 and newer)
The most efficient way to combine ranges while automatically ignoring empty cells.
The TEXTJOIN function is specifically designed to combine text from multiple ranges or arrays. By setting its second argument to TRUE, it automatically ignores any empty cells, guaranteeing a clean result without consecutive delimiters.
Click on the cell where you want the combined text to appear.
Type the formula =TEXTJOIN(", ", TRUE, D2:H2). Replace D2:H2 with the actual range of cells you wish to combine.
Press Enter. The TRUE argument explicitly tells Excel to ignore any blank cells in the specified range, successfully preventing extra commas.
Use CONCATENATE with IF Statements for Older Excel Versions
A reliable workaround for Excel 2016 and older versions that lack the TEXTJOIN function.
Easily Combine Cells with TEXTJOIN in WPS Spreadsheet
WPS Spreadsheet fully supports the modern TEXTJOIN function, allowing you to merge cells seamlessly while ignoring blanks. It offers complete formula compatibility with Excel in a lightweight, user-friendly interface.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the data you want to merge.
- 2. Enter the TEXTJOIN formula: Click on your target cell and type =TEXTJOIN(", ", TRUE, A1:E1), adjusting the range to fit your columns.
- 3. Execute the formula: Press Enter. The formula executes identically to Excel, effortlessly merging your data without extra separators.
Frequently Asked Questions
Why does TEXTJOIN result in a #NAME? error?
The #NAME? error occurs if you are using a version of Excel older than 2019 that does not support the TEXTJOIN function. You will need to upgrade your Office suite, switch to WPS Office, or rely on the traditional CONCATENATE workaround.
Can I use multiple different delimiters in TEXTJOIN?
Yes, you can use an array constant for the delimiter argument. For example, using =TEXTJOIN({", ","-"}, TRUE, A1:C1) will alternate between a comma and a hyphen as delimiters when combining the cell values.
Does TEXTJOIN work correctly with numbers and dates?
TEXTJOIN can combine numbers and dates, but dates might be converted to their underlying serial numbers. To preserve your specific date formatting, wrap the date cell reference in the TEXT function within your formula, like TEXT(A1, "mm/dd/yyyy").
What is the difference between CONCAT and TEXTJOIN?
While both functions can combine ranges, the CONCAT function does not accept a delimiter argument and cannot automatically ignore blank cells. TEXTJOIN is much more flexible because it allows you to specify a separator and a TRUE/FALSE flag for empty cells.
