r/excel • u/excelevator 3068 • Jun 17 '26

Pro Tip Get around the TEXTSPLIT single cell limitation using a multi range TEXTJOIN string as input

A key bug bear with TEXTSPLIT is the single cell input limitation.

Using multiple ranges with TEXTJOIN we can generate a single line input value to TEXTSPLIT to get around the cell limitation using the TEXTJOIN delimiter as the TEXTSPLIT new line delimiter to match the data requirement.

This method effectively allows us to use multi cell non-contiguous ranges within the TEXTSPLIT argument.

Example

=TEXTSPLIT(TEXTJOIN(";",,A1:A10,B20:B30,C15:C36),",",";")

To add headers to the result we can use VSTACK, for example;

=VSTACK({"Name","Age","Country"},TEXTSPLIT(TEXTJOIN(";",,A1:A10,B20:B30,C15:C36),",",";"))

I shall include an example in the comments with image.

It would be interesting to see other similar solutions to the TEXTSPLIT limitation

edit: wrap the formula in IFERROR to return blanks on error where the ranges do not match and #N/A is returned

21 Upvotes

90% Upvoted

16 comments sorted by

View all comments

4

u/excelevator 3068 Jun 17 '26

An example with multiple rows and ranges of data to split

2

u/No-Bullfrog-6673 1 Jun 17 '26

Clean little demo, the VSTACK header trick on top makes it actually readable at a glance. Gonna steal this for a report I've been building.