Convert a Comma-Separated List into WPS Spreadsheet Table Rows Without VBA
Question details
The user needs a formula-only method in WPS Spreadsheets to split a comma-separated value in each Excel table row into separate rows, using structured references and without VBA.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A table has a column such as Change Group containing comma-separated items, and the user wants each item to appear in its own table row while keeping the table structure intact.
- Observed behavior
- The goal is to return the first, second, third, and subsequent values in separate table rows through a single formula.
Make sure your data is formatted as an Excel table and that your version of WPS Spreadsheets supports the TEXTSPLIT function. Confirm the exact table name and column header because they must match the structured references in the formula.
Use TEXTSPLIT with structured references to return each list item in its own row
Use this single formula when you need a non-VBA, formula-driven way to expand comma-separated values into separate rows in a WPS spreadsheet table.
The formula uses TEXTSPLIT to split the comma-separated text in the current row. The ROW calculation returns the relative row position within the table body, so INDEX retrieves the first, second, third, and later items as the formula is filled down.
Click the cell where you want the first separated value to appear, normally in an empty column of the same table.
Type =IFERROR(INDEX(TEXTSPLIT([@[Change Group]],","),ROW([@[Change Group]])-ROW(Analysis_T[[#Headers],[Change Group]])),"") and adjust Analysis_T and Change Group to match your table name and column header.
Drag the fill handle down through the table rows, or press Enter in a table column so each row calculates its corresponding item from the comma-separated list.
Split comma-separated Excel table values into rows directly in WPS Spreadsheets
WPS Spreadsheets can perform the same structured-reference formula task without VBA. Use TEXTSPLIT and INDEX to expand each comma-separated list item into its own row while keeping your table layout and formulas manageable.
- 1. Open your Excel table in WPS Spreadsheets: Launch WPS Office, open the workbook, and confirm the data range is formatted as an Excel table with the list column visible.
- 2. Select the output cell and enter the formula: In an empty table column cell, enter the TEXTSPLIT/INDEX formula with your actual table name and column header, for example =IFERROR(INDEX(TEXTSPLIT([@[Change Group]],","),ROW([@[Change Group]])-ROW(Analysis_T[[#Headers],[Change Group]])),"").
- 3. Copy the formula down the table: Press Enter and drag the fill handle down the column so each row extracts its corresponding comma-separated item.
- 4. Check and format the results: Review the split values, rename or format the column if needed, and continue working with the expanded table data.
Frequently Asked Questions
Can I use this formula if my table or column has a different name?
Yes. Replace Analysis_T with your actual table name and Change Group with your actual column header. You can check the table name in Table Tools or Name Manager in WPS Spreadsheets.
Why does my formula return a #NAME? error?
This usually means your WPS Spreadsheets version does not support TEXTSPLIT or the function name is misspelled. Update WPS Office to a version that supports dynamic array functions, and check that structured references are typed with correct brackets.
Will this method work for semicolon-separated or space-separated lists?
Yes. In the TEXTSPLIT first argument's delimiter, replace the comma with your delimiter, such as ";" or " ". The formula then splits by that character.
Do I need to sort or filter the table for this to work?
No, but the formula expects the table body to start immediately below the header and the column to be filled continuously. If you filter rows, the ROW argument still refers to the worksheet row, so hidden rows can affect the displayed result; remove filters while generating the expanded values.
Can I use another method if TEXTSPLIT is not available?
For one-time splitting, you can use Text to Columns or Power Query in WPS Spreadsheets. For a repeatable formula expansion, update WPS Office to a version that supports TEXTSPLIT.
