Workaround for the TEXTSPLIT function – LAMBDA function
The TEXTSPLIT function splits a string into an array based on delimiting values.
Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns only the first value in each cell. See the image above.
Formula in cell D3:
There is a workaround for this.
Join the cells using the TEXTJOIN function before splitting the strings.
Formula in cell D3:
This workaround is fine if the total character length is not above 32767 characters.
The image above shows a scenario where the total character count exceeds 32767 characters, the formula returns #CALC! error.
There is a workaround for this as well, the Excel 365 formula below doesn't have this limitation.
Excel 365 formula in cell E3:
Explaining the formula in cell E3
Step 1 - Split value into substrings
The TEXTSPLIT function splits a string into an array based on delimiting values.
Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
TEXTSPLIT(y, " ")
Step 2 - Add arrays vertically
The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Function syntax: VSTACK(array1,[array2],...)
VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))
Step 3 - Create a LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))
Step 4 - Pass each cell value in cell range B3:B5 to the LAMBDA function
The REDUCE function applies each value in a cell range or array to a LAMBDA function, the result is a total value.
In this example, it is the opposite, each cell value is split into multiple substrings and the VSTACK function adds the arrays. The result is a larger array than the original size.
REDUCE(,B3:B5,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))))
returns
{"A","A","A", ... ,"A";
"B","B","B", ... ,"B";
"C","C","C", ... ,"C"}
Text string manipulation category
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.