Data Import & Export

How to Split Multiple Numbers into Rows in Excel While Keeping Related Data

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to split multiple numbers and associated text strings from a single cell into separate rows while retaining the corresponding values in adjacent columns.

How to Split Multiple Numbers into Rows in Excel While Keeping Related Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Restructuring messy or combined datasets where multiple delimited values are grouped into single cells, but need to be analyzed row by row.
Observed behavior: Data is currently clumped into single cells separated by delimiters, which prevents proper row-based sorting, filtering, or pivot table analysis without losing the relationship to adjacent data.

Before you start

Verify your Excel version, as the dynamic array formula method requires Excel 365. If you are using an older version like Excel 2016 or 2019, you will need to use the Power Query method instead.

Solution 1Recommended

Use Dynamic Array Formulas (Excel 365)

Utilize Excel 365 functions like LET, TEXTSPLIT, and TOCOL to dynamically split values into new rows while keeping related column data.

This method relies on Excel 365 exclusive functions such as TEXTSPLIT, VSTACK, HSTACK, and TOCOL.

The LET function is used to define variables, making the complex array formula easier to read and process.

Select the Destination Cell

Click on the empty cell where you want the new, separated dataset to begin spilling.

Enter the Formula

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

Adjust Ranges and Delimiters

Change A2:A3 to the range containing the cells you want to split, change B2:D3 to your related columns, and update the delimiter ", " if your numbers are separated by a different character.

Execute the Formula

Press Enter. The formula will automatically spill the split numbers into individual rows alongside their corresponding related data.

Version Compatibility: If you see a #NAME? error, your version of Excel does not support these dynamic array functions. Please proceed to the Power Query solution.

Solution 2

Use Power Query to Split Values into Rows

Power Query provides a user-friendly interface to split delimited values into rows without writing complex formulas, and it works on older Excel versions.

Split and Manage Data Efficiently with WPS Spreadsheets

WPS Office offers powerful, user-friendly data handling features, including built-in Text to Columns and Paste Special tools, allowing you to restructure complex datasets quickly.

1. Use Text to Columns: Highlight the cells containing the combined numbers. Go to the Data tab, click 'Text to Columns', and split the values using your specific delimiter.
2. Copy the Separated Data: Once the values are split across multiple columns, highlight and copy the newly generated cells.
3. Transpose to Rows: Right-click your destination cell, select 'Paste Special', check the 'Transpose' box, and click OK to convert the columns into rows alongside your related data.

Seamlessly opens, edits, and saves Microsoft Excel (.xlsx) files without formatting loss.Includes versatile data management tools like Text to Columns, Transpose, and advanced sorting.Free, lightweight, and features an intuitive tabbed interface for managing multiple spreadsheets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the TEXTSPLIT formula returning a #NAME? error?

TEXTSPLIT, TOCOL, VSTACK, and HSTACK are dynamic array functions introduced in Excel 365. If you are using an older version (like Excel 2016 or 2019), Excel does not recognize the function names, resulting in a #NAME? error. You should use Power Query instead.

Can I use standard Text to Columns to split data into rows directly?

No, the built-in Text to Columns feature exclusively splits data horizontally into multiple columns. To convert them into rows, you must first use Text to Columns, copy the results, and use Paste Special > Transpose.

Does using Power Query alter my original spreadsheet data?

No, Power Query safely extracts a copy of your original data into its editor. Once you apply the splitting transformations and click Close & Load, it outputs the new table into a separate worksheet, leaving your source data unchanged.

Excel & Data Problems