Function Problems

Combine Cells Without Duplicates Using TEXTJOIN in Excel

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

Question details

Combine values from multiple cells into one result, excluding blanks and duplicates and separating the remaining values.

Combine Cells Without Duplicates Using TEXTJOIN in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In the worksheet, click an empty cell outside the source range BW13:BY13.

2
Enter the formula

In the formula bar, enter =IFERROR(TEXTJOIN("/ ",FALSE,UNIQUE(FILTER(BW13:BY13,BW13:BY13<>""),TRUE)),"") and press Enter.

3
Adapt the range or separator

Replace both instances of BW13:BY13 with your horizontal source range. To use another separator, replace "/ " in the formula with the text you prefer.

Combine unique nonblank values with a dynamic-array formula
Check unexpected blank results: IFERROR also hides errors unrelated to an empty range. If the result is unexpectedly blank, temporarily remove IFERROR to inspect the underlying error.
Combine values in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and select File > Open to choose your workbook.
  2. 2. Select a result cell: Click an empty cell outside the row range you want to combine.
  3. 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. 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.
Combine unique nonblank values with a custom separatorOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork without VBA for this task
microsoft office alternative - wps office

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.