Function Problems

Split Multiple Rows with TEXTSPLIT in Excel Without CONCAT Limits

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Split semicolon-delimited values from multiple cells with one dynamic-array formula, without joining the entire range into one string.

Split Multiple Rows with TEXTSPLIT in Excel Without CONCAT Limits
Product
Microsoft Excel for Microsoft 365
Device & OS
not provided
Scenario
Each cell in the Values column of Table2 contains text to split into separate columns, with one output row per source cell.
Observed behavior
Joining the source range with CONCAT or TEXTJOIN can exceed Excel's 32,767-character cell limit. A direct MAP formula may also fail when each TEXTSPLIT result contains multiple values.
Before you start

Confirm that your Excel version supports TEXTSPLIT, REDUCE, VSTACK, DROP, and dynamic arrays. Check that Table2[Values] is the correct source column and that there is clear space outside the table for the spilled results.

Solution 1Recommended

Split each source cell and stack the results with REDUCE

Use one formula to split each input row independently, avoiding a single combined text string.

REDUCE processes each source cell in turn, while VSTACK adds its split values as a new output row. This avoids the combined-string limit, although each individual source cell is still subject to Excel's cell limit.

Rows with fewer split values are padded with blanks. The formula also replaces #N/A errors in the assembled output with blanks, so use it when those errors do not need to be preserved.

1
Choose an output cell

Click an empty cell outside Table2, leaving enough clear rows and columns for the results to spill.

2
Enter the formula

Enter =DROP(REDUCE("",Table2[Values],LAMBDA(acc,x,IFNA(VSTACK(acc,IF(x="","",TEXTSPLIT(x,";"))),""))),1) and press Enter. Replace Table2[Values] with your source column if needed.

3
Check the spill

Verify that each source cell appears on its own output row and that semicolons separate the values into columns. If Excel shows #SPILL!, clear any cells blocking the output range.

Split each source cell and stack the results with REDUCE
Large ranges: The formula builds the result incrementally, so it may be slow for large ranges. The output must also fit within Excel's worksheet row and column limits.
Free Microsoft Office alternative

Work with delimited spreadsheet data in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Excel format compatibility for a smoother migration. The Excel-specific dynamic-array formula in this article is not guaranteed to work in WPS Spreadsheets.

Open and edit Microsoft Excel XLSX and XLS filesWork with spreadsheet data in a familiar interfaceUse spreadsheet tools to organize delimited dataMove existing Office files into a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why can CONCAT or TEXTJOIN fail on a large range?

Joining all source cells creates one text string, which can exceed Excel's 32,767-character cell limit. Processing each cell separately avoids that combined-string limit.

Why doesn't MAP with TEXTSPLIT always work?

TEXTSPLIT can return multiple columns for each source cell, while MAP expects compatible results from its iterations. REDUCE with VSTACK assembles those results into a rectangular array instead.

Can I put the formula inside an Excel table?

Enter the formula in an empty cell outside the table. Spilled dynamic-array formulas are not supported inside Excel tables.

What happens when source rows contain different numbers of values?

VSTACK aligns the split results in a rectangular array. In the recommended formula, IFNA replaces the padding errors in shorter rows with blanks.

How do I split on a different delimiter?

In the formula, replace the semicolon in TEXTSPLIT(x,";") with your delimiter, such as a comma: TEXTSPLIT(x,",").