Function Problems

Split Mixed Numbers and Text into Separate Excel Rows

Aamir Naveed AkramAamir Naveed Akram Nov 12, 2026 868 views

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.

Split Mixed Numbers and Text into Separate Excel Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Create or confirm the named range

Select the cell or range containing the space-separated list, then go to Formulas > Name Manager > New and name the range mixedList.

2
Select an empty output cell

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.

3
Enter the dynamic array formula

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))

4
Review the spilled results

Excel spills one result per row. Edit the source value in mixedList when needed; the formula output updates automatically.

Use a Microsoft 365 dynamic array formula to pair numbers with following text
Formula requirement: This solution is designed for Microsoft 365. Earlier Excel versions that do not include dynamic array functions cannot use this formula as written.
Work with mixed lists in WPS Spreadsheets

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook or create a new spreadsheet.
  2. 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. 3. Choose an empty result area: Click a blank cell with sufficient empty cells below it so formula results can expand into rows.
  4. 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. 5. Save in Excel format if needed: Select Menu > Save As and choose Excel Workbook (*.xlsx) to share the resulting worksheet with Excel users.
Organize number-and-text pairs into a clear row-based worksheet result.Use a familiar spreadsheet interface for formula entry and range management.Open and save Microsoft Excel .xlsx files with strong format compatibility.Move existing Excel worksheets to WPS Spreadsheets with a lightweight office suite.
microsoft office alternative - wps office

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.