Function Problems

WPS Office Excel Formula to Replace 2XL with 3XL and Remove Duplicates

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 868 views

Question details

The user needs an Excel formula that checks selected size columns, changes any 2XL value to 3XL, removes duplicate results, and joins the remaining sizes into one text string.

WPS Office Excel Formula to Replace 2XL with 3XL and Remove Duplicates
Product
WPS Office Excel
Device & OS
not provided
Scenario
The user is working with a size data range and wants to normalize 2XL entries to 3XL, filter based on a TRUE condition, remove duplicate sizes, and combine the remaining values into one text string.
Observed behavior
The desired output is a comma-separated text string of unique sizes with every 2XL value replaced by 3XL.
Before you start

Open the workbook in WPS Office Excel and identify the actual size range and the criteria range that contains TRUE/FALSE values. If you use named ranges, confirm that the name size points to the correct cells before entering the formula.

Solution 1Recommended

Use LET with SWITCH, FILTER, UNIQUE, and TEXTJOIN to Replace 2XL with 3XL and Remove Duplicates

Use this single-formula method when the size values are in a horizontal range and you need to normalize sizes, remove duplicates, and join the remaining values in one step.

This formula stores the SWITCH result in swap, filters to the columns where the criteria row is TRUE, removes duplicates, and joins the remaining values with a comma and space. It updates automatically when the source cells change.

1
Click the output cell

Select the empty cell where you want the cleaned size list to appear, for example K2.

2
Enter the LET formula

Type =LET(swap,SWITCH(size,"2XL","3XL",size),filtered,UNIQUE(FILTER(swap,E2:J2="true"),1),TEXTJOIN(", ",1,filtered)) and adjust the named range size and the criteria range E2:J2 to match your workbook.

3
Press Enter and verify the result

Press Enter and confirm that every 2XL value is displayed as 3XL, duplicate sizes are removed, and the remaining sizes are joined into one text string.

Use LET with SWITCH, FILTER, UNIQUE, and TEXTJOIN to Replace 2XL with 3XL and Remove Duplicates
Formula updates automatically: If the source values change, the result recalculates automatically. If the criteria range contains no TRUE values, the formula may return an empty result or an error.
WPS Office Excel

Clean Size Lists in WPS Office Excel with a Single Dynamic Formula

Use WPS Office Excel to enter a LET, SWITCH, FILTER, UNIQUE, and TEXTJOIN formula that replaces 2XL with 3XL, filters the selected size columns, removes duplicates, and joins the remaining sizes into one text string.

  1. 1. Open the workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the size and criteria columns.
  2. 2. Select the output cell: Click the cell where you want the combined size result to appear, such as K2.
  3. 3. Enter the formula: Type =LET(swap,SWITCH(size,"2XL","3XL",size),filtered,UNIQUE(FILTER(swap,E2:J2="true"),1),TEXTJOIN(", ",1,filtered)) and adjust the ranges to match your data.
  4. 4. Press Enter and check the result: Press Enter and verify that 2XL values now appear as 3XL, duplicates are removed, and the remaining sizes are joined into one text string.
  5. 5. Save as .xlsx for compatibility: Save the workbook in .xlsx format so it can be opened and recalculated in Microsoft Excel if needed.
Replaces 2XL with 3XL and removes duplicates in one workbook formulaAutomatically joins remaining sizes into a clean text stringFull compatibility with Microsoft Excel .xlsx formulas and workbook structureFree, lightweight WPS Office with a familiar ribbon interfaceWorks across Windows, macOS, Linux, Android, and iOS
QA img-9

Frequently Asked Questions

What should I do if the formula returns #NAME? in WPS Office Excel?

Update to a version of WPS Office Excel that supports LET, SWITCH, FILTER, and UNIQUE. If the error continues, check that you are not using older compatibility mode or rebuild the formula with helper columns.

How do I replace 2XL with 3XL without the named range size?

Replace size in the SWITCH function with your actual size range, such as A2:J2. For example: SWITCH(A2:J2,"2XL","3XL",A2:J2).

Can I use this formula when the TRUE/FALSE criteria are in a separate row?

Yes. Change E2:J2="true" to the row or range that contains your TRUE/FALSE indicators and keep the same number of columns as the size range.

Why is UNIQUE using a second argument of 1?

The 1 tells UNIQUE to compare columns instead of rows, which is useful when the FILTER result is a horizontal series of size values. Remove or change it if your filtered result is vertical.