Split Mixed Numbers and Text into Separate Excel Rows
Question details
Create a separate row for each number in a space-separated list and pair it with the text value that immediately follows it.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A named range called mixedList contains alternating numeric items and optional text items that need to be reorganized into a spilled list.
- Observed behavior
- The desired result is one output row per number, with its associated following text displayed beside it in the same spilled result.
Make sure mixedList is a single space-separated list and that the cells below the formula are empty so the dynamic array can spill. This formula requires Microsoft 365 functions including LET, TEXTSPLIT, VSTACK, DROP, and TOCOL.
Use a Microsoft 365 dynamic array formula to pair numbers with following text
Use this method when each numeric item is followed by zero or one text item and you want Excel to return the matching pairs as separate rows automatically.
The formula splits mixedList at spaces, tests which entries are numeric, shifts the text entries one position to align them with the preceding numbers, and removes blank or error-only results from the final column. For example, a list such as "101 Red 205 Blue 310" returns 101 Red, 205 Blue, and 310.
Select the cell or range containing the space-separated list, then go to Formulas > Name Manager > New and name the range mixedList.
Click the first blank cell where you want the row-by-row results to begin. Leave the cells below it clear for the spilled array.
Type or paste this formula and press Enter: =LET(groups,TEXTSPLIT(mixedList,," "),isText,ISERROR(VALUE(groups)),numbers,IF(isText,NA(),groups),text,VSTACK(DROP(IF(isText,groups,""),1),""),TOCOL(numbers&" "&text,3))
Excel spills one result per row. Edit the source value in mixedList when needed; the formula output updates automatically.

Split Number and Text Pairs with WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet environment for organizing mixed number-and-text lists. Paste your data into a worksheet, use supported dynamic array formulas where available, and review the spilled output in rows before saving or sharing the workbook.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook or create a new spreadsheet.
- 2. Place the mixed list in a source cell: Paste the space-separated values into a worksheet cell, then select that cell or range for use as the formula source.
- 3. Choose an empty result area: Click a blank cell with sufficient empty cells below it so formula results can expand into rows.
- 4. Enter and test the formula: Enter a compatible dynamic array formula in the selected output cell, press Enter, and check that each number is paired with the following text item.
- 5. Save in Excel format if needed: Select Menu > Save As and choose Excel Workbook (*.xlsx) to share the resulting worksheet with Excel users.

Frequently Asked Questions
What does the mixedList name refer to in the formula?
mixedList is a named range containing the source list. Create it from Formulas > Name Manager > New, or replace mixedList in the formula with a direct cell reference such as A1.
Why does Excel show a #SPILL! error after I enter the formula?
A #SPILL! error means one or more cells in the intended output range are not empty. Clear or move the blocking cells below the formula cell, then enter the formula again.
Can this formula handle text before the first number?
No. The formula is intended for a list that starts with a number and pairs each number with the text item immediately after it. Remove leading text or restructure the source list first.
Can one number be associated with multiple following words?
Not with this exact formula. It pairs a number with one immediately following text item. If a label contains multiple words, use a different delimiter or revise the formula to group all text items until the next number.




