Function Problems

How to Split Text into Rows and Repeat Related Values using Formulas

Rana Garcia Sep 25, 2026 869 views

Question details

The user needs to split text strings from one column into multiple separate rows while duplicating the corresponding reference values from an adjacent column to maintain data association.

Split Text into Rows and Repeat Related Values with Dynamic Arrays

Product: Spreadsheets
Device & OS: not provided
Scenario: Transforming unstructured, delimited text lists into a normalized tabular dataset for accurate data analysis and reporting.
Observed behavior: A two-column dataset with grouped delimited text needs to be flattened into a standard array where every split item gets its own row paired with its original category identifier.

Before you start

Ensure your spreadsheet software is updated to a version that supports modern dynamic array functions such as LET, REDUCE, VSTACK, and TEXTSPLIT.

Solution 1Recommended

Use the LET, REDUCE, and TEXTSPLIT Functions

This is the most robust and recommended approach for handling multiple rows of delimited text dynamically.

By combining the REDUCE and LAMBDA functions, you can iterate through an entire range of cells. TEXTSPLIT breaks the text apart, while VSTACK and HSTACK recombine the text with the related column value.

Select the destination cell

Click on an empty cell (e.g., D2) where you want the new, expanded table to begin spilling its results.

Enter the REDUCE and TEXTSPLIT formula

Type the following formula into the formula bar: =LET(spt,DROP(REDUCE(" ",B2:B4,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1),HSTACK(TOCOL(IF(spt<>"",A2:A4),2),TOCOL(spt,2)))

Apply the array

Press Enter to execute the formula. The results will automatically spill down and across, creating a clean two-column list.

Use the LET, REDUCE, and TEXTSPLIT Functions

Dynamic Spill: Since this is a dynamic array formula, you only need to enter it into the top-left cell; the surrounding cells must be empty for the results to populate correctly.

Solution 2

Use MAP, TEXTJOIN, and TEXTSPLIT Alternative

Use this alternative if you prefer to join all strings first and then split them, handling variable lengths dynamically.

Solution 3

Use Power Query for Automated Splitting

Ideal for repetitive tasks where the data source is updated frequently and needs strict automated transformation.

Advanced Data Processing in WPS Spreadsheet

Easily Split and Transform Data with WPS Spreadsheet

WPS Spreadsheet fully supports powerful dynamic array functions like TEXTSPLIT, VSTACK, and LET, enabling you to manipulate complex datasets, automate repetitive tasks, and clean data effortlessly without switching tools.

1. Open your workbook: Launch WPS Office and open your dataset in WPS Spreadsheet.
2. Select a blank cell: Click on the cell where you want your transformed table to appear.
3. Apply dynamic formulas: Type or paste your LET and TEXTSPLIT formula, press Enter, and let WPS Spreadsheet automatically expand the array.

Full support for modern dynamic array formulas and functionsHighly compatible with Microsoft Excel file formats (.xlsx)Lightweight, fast execution for heavy data processing tasksFamiliar, easy-to-use interface with no steep learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return a #SPILL! error?

A #SPILL! error occurs when the dynamic array formula does not have enough blank cells to display all the resulting data. Ensure that the destination range below and to the right of your formula is completely empty.

What delimiters can I use with the TEXTSPLIT function?

You can use any character or string as a delimiter in TEXTSPLIT. Common examples include spaces (" "), commas (","), or semicolons (";"). You simply specify the chosen character inside quotation marks within the formula.

Can I split text into columns instead of rows?

Yes. The TEXTSPLIT function has separate arguments for column delimiters and row delimiters. By putting your delimiter character in the column delimiter argument and omitting the row delimiter, the text will split horizontally across columns.

Do I need to press Ctrl+Shift+Enter for these array formulas?

No. In software versions that support dynamic arrays (like modern WPS Spreadsheet and Excel 365), you only need to press Enter. The formula will automatically spill the results.

Excel & Data Problems