## 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.

**Contact Oscar**

You can contact me through this contact form