How to Hide Commas When Combining Blank Cells in Excel
Question details
The user needs a formula to combine first and last names from separate cells with a comma, while ensuring no orphaned comma is displayed if one or both source cells are blank.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Combining data entries like first and last names from different columns into a single cell using a comma as a separator.
- Observed behavior
- When using basic CONCAT or string concatenation, a comma remains visible in the result cell even when the source data cells are empty.
Verify the exact cell references containing your first and last names, and check if your spreadsheet software version supports the TEXTJOIN function, which simplifies handling blank cells.
Use the TEXTJOIN Function to Ignore Blank Cells
The TEXTJOIN function is the most efficient way to combine text because it includes a built-in argument to automatically ignore blank cells, preventing extra delimiters like commas.
TEXTJOIN was introduced in newer versions of Excel and is fully supported in WPS Spreadsheet. It allows you to specify a delimiter, choose whether to ignore empty cells, and select the range of cells to combine.
Click on the cell where you want the combined names to appear.
Type the formula: =TEXTJOIN(", ", TRUE, DataSheet!B16, DataSheet!A16) into the formula bar.
Press Enter. The 'TRUE' argument tells the formula to ignore any blank cells, so the comma separator will only appear if both names are present.
Use IF and COUNTA to Conditionally Combine Cells
If you are using an older spreadsheet version that lacks the TEXTJOIN function, combining the IF, COUNTA, and CONCAT functions provides a reliable workaround.
Easily Combine Text and Handle Blank Cells with WPS Spreadsheet
WPS Spreadsheet fully supports advanced text functions like TEXTJOIN, CONCAT, and IF, allowing you to seamlessly combine text, ignore blanks, and format your data without dealing with messy leftover commas.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the names.
- 2. Apply the TEXTJOIN function: Click on your target cell and type =TEXTJOIN(", ", TRUE, B2, A2).
- 3. Drag to fill: Press Enter, then drag the fill handle down to apply the clean formatting to your entire list automatically.
Frequently Asked Questions
Why does CONCATENATE leave commas when cells are blank?
The older CONCATENATE function directly stitches together whatever values you provide, including hardcoded strings like commas ", ". It does not possess built-in logic to evaluate whether the adjacent cell is empty before inserting the text.
Can I hide the comma if only the last name is missing?
Yes, using the TEXTJOIN function with the 'ignore_empty' argument set to TRUE handles this automatically. It only places the delimiter between non-empty text strings, ensuring no trailing commas appear.
What if my spreadsheet version doesn't have the TEXTJOIN function?
You can use the IF function to evaluate the cells first. For example, using =IF(ISBLANK(B2), A2, A2 & ", " & B2) will check if B2 is blank and output just A2, otherwise it will output both separated by a comma.
How do I combine cells with a space instead of a comma?
Simply change the delimiter in your formula. For TEXTJOIN, replace ", " with " " in the first argument: =TEXTJOIN(" ", TRUE, A2, B2). This will insert a space between the values.
