WPS Office Excel Formula to Replace 2XL with 3XL and Remove Duplicates
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.

- 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.
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.
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.
Select the empty cell where you want the cleaned size list to appear, for example K2.
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.
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.

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. Open the workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the size and criteria columns.
- 2. Select the output cell: Click the cell where you want the combined size result to appear, such as K2.
- 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. 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. Save as .xlsx for compatibility: Save the workbook in .xlsx format so it can be opened and recalculated in Microsoft Excel if needed.

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.




