Split Multiple Rows with TEXTSPLIT in Excel Without CONCAT Limits
Question details
Split semicolon-delimited values from multiple cells with one dynamic-array formula, without joining the entire range into one string.

- 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.
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.
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.
Click an empty cell outside Table2, leaving enough clear rows and columns for the results to spill.
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.
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.

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.

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,",").




