Combine Cells Without Duplicates Using TEXTJOIN in Excel
Question details
Combine values from multiple cells into one result, excluding blanks and duplicates and separating the remaining values.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A user wants to join values across a row without using VBA.
- Observed behavior
- The result should contain each nonblank value once, with separators only between values and an empty result when the source range is blank.
Confirm that your version of Excel supports the FILTER and UNIQUE dynamic-array functions. Identify the horizontal source range and select an empty cell outside it for the result.
Combine unique nonblank values with a dynamic-array formula
Use this formula in Microsoft 365 to join values across a row and return a blank result when all source cells are empty.
FILTER excludes blanks, UNIQUE with TRUE compares columns in the horizontal range, and TEXTJOIN places the separator between the remaining values. IFERROR returns an empty string if the formula encounters an error.
In the worksheet, click an empty cell outside the source range BW13:BY13.
In the formula bar, enter =IFERROR(TEXTJOIN("/ ",FALSE,UNIQUE(FILTER(BW13:BY13,BW13:BY13<>""),TRUE)),"") and press Enter.
Replace both instances of BW13:BY13 with your horizontal source range. To use another separator, replace "/ " in the formula with the text you prefer.

Join unique cell values in WPS Spreadsheet
Use the same formula in a version of WPS Spreadsheet that supports TEXTJOIN, UNIQUE, and FILTER to combine nonblank values from a row.
- 1. Open the workbook: Open WPS Spreadsheet and select File > Open to choose your workbook.
- 2. Select a result cell: Click an empty cell outside the row range you want to combine.
- 3. Enter the formula: In the formula bar, enter =IFERROR(TEXTJOIN("/ ",FALSE,UNIQUE(FILTER(BW13:BY13,BW13:BY13<>""),TRUE)),"") and replace both range references with your source range.
- 4. Check the result: Press Enter and verify that each nonblank value appears once. If the formula is unsupported, check whether your WPS Spreadsheet version provides all three functions.

Frequently Asked Questions
Why does the formula use TRUE in UNIQUE?
TRUE tells UNIQUE to compare columns, which removes duplicates across a horizontal range such as BW13:BY13.
Why did FILTER return a #CALC! error when every cell was blank?
FILTER has no matching values to return. Wrapping the formula in IFERROR with an empty string as its second argument displays a blank result instead.
How do I change the separator?
In the TEXTJOIN formula, replace "/ " with another quoted separator, such as ", " for a comma and a space.
Do I need VBA to combine these cells?
No. Enter the TEXTJOIN, UNIQUE, and FILTER formula directly in a worksheet cell in a version of Excel that supports these functions.




