Keep Only the First Six Comma-Separated Values in WPS Office Excel
Question details
The user needs to remove every comma-separated item after the sixth value while keeping the first six comma-separated values in each Excel cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains cells with comma-separated lists longer than six items, and the user wants to truncate each cell to only the first six values.
- Observed behavior
- Cells currently contain more than six comma-separated values, and all items after the sixth value need to be removed without manually editing each row.
Confirm that your source data is in a single column and insert an empty helper column next to it before entering the formula.
Use SUBSTITUTE, LEFT, and TRIM to keep only the first six values
Best when you have a long comma-separated list in a cell and want a non-destructive formula that returns only the first six values.
This formula replaces the sixth comma with a large block of spaces and then extracts the first 255 characters from the left. TRIM removes the extra spaces, leaving at most six clean comma-separated values. The same formula works in WPS Office Spreadsheets and Excel.
In a blank column next to your source data, click the cell on the same row as the first value, for example G2.
Type =TRIM(LEFT(SUBSTITUTE(F2,",",REPT(" ",255),6),255)) and press Enter. Replace F2 with your actual source cell reference if needed.
Drag the fill handle from G2 down through the remaining rows to apply the formula to each cell.
Copy the formula results, right-click the original data column, choose Paste Special > Values, and then delete the helper column.
Keep Only the First Six Comma-Separated Values with WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the Excel-compatible SUBSTITUTE, LEFT, and TRIM formula to return only the first six comma-separated values from each cell. The formula works directly in WPS Office without changing your file format.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook containing the comma-separated values, or paste your list into a column such as column F.
- 2. Enter the formula in a helper cell: Click the first blank helper cell, such as G2, type =TRIM(LEFT(SUBSTITUTE(F2,",",REPT(" ",255),6),255)), and press Enter.
- 3. Fill the formula down: Drag the fill handle from G2 down through the rest of the data rows to process each cell.
- 4. Paste the results over the original data: Select the formula results, press Ctrl+C, right-click the original source column, choose Paste Special > Values, then remove the helper column.
Frequently Asked Questions
What should I change if my source text is not in F2?
Replace F2 with the actual cell reference in both the SUBSTITUTE and LEFT parts of the formula, for example =TRIM(LEFT(SUBSTITUTE(A2,",",REPT(" ",255),6),255)).
Will this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the same SUBSTITUTE, LEFT, and TRIM functions as Excel, so the formula works without any changes.
How do I permanently keep only the first six values?
Copy the formula results, right-click the original data column, choose Paste Special > Values to replace the original data, and then delete the helper column.
What happens if a cell has fewer than six comma-separated values?
The formula returns the entire cell content because there is no sixth comma to replace, so cells with five or fewer values are not truncated.
