SOLVED

# Textsplit selecting a range.

Steel Contributor

# Textsplit selecting a range.

Hi,

Just confused using TEXTSPLIT. It does not split the cells when i select a range/liste of valuer. But only one cell.

=TEXTSPLIT(B4:B16;" "), return the value before space " ")

=TEXTSPLIT(B4;" ") return the value before and after the space

(Tekstdeling = Textsplit)

Is it a bug, or is it supposed to work like that?

Best Regards

Geir

6 Replies
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

# Re: Textsplit selecting a range.

``=DROP(REDUCE("",SEQUENCE(ROWS(A3:A15)),LAMBDA(x,y,IFNA(VSTACK(x,TEXTSPLIT(INDEX(A3:A15,y,1)," ")),""))),1)``

An alternative could be the above formula.

TEXTSPLIT can't spill down and to the right at the same time. Spilling down and to the right at the same time would mean to return an array of arrays which in general isn't possible. Actually the array of arrays returns an error with e.g. LAMBDA and BYROW. For =TEXTSPLIT(A3:A15," ") it means that the intended result can't be returned.

In the example in the screenshot =TEXTSPLIT(A3," ") would create a horizontal array with 2 columns and =TEXTSPLIT(A11," ") would create a horizontal array with 8 columns.

If we tried

=BYROW(A3:A15,LAMBDA(x,TEXTSPLIT(x," ")))

it would result in a CALC! error because of nested arrays (or array of arrays). The LAMBDA and BYROW would need to spill down and to the right at the same time which isn't possible with LAMBDA and BYROW (but with LAMBDA and REDUCE).

# Re: Textsplit selecting a range.

Great, thank you for the explanation.

# Re: Textsplit selecting a range.

The workaround could be

``````=TRIM(
TEXTSPLIT(
TEXTJOIN( UNICHAR(9999),,\$B\$21:\$B\$33 ),
",", UNICHAR(9999)
)
)``````

# Re: Textsplit selecting a range.

This solution is probably not the fastest but it's fun to write a recursive function to get around TEXTSPLIT's limitations.  This is written to do a basic split by column delimiter:

``````Text2Columns = LAMBDA(arr, col_delim,
LET(
timer, VSTACK(col_delim, 1),
next, TAKE(arr, 1, 1),
split, TEXTSPLIT(@next, @col_delim, , 1, , ""),
acc, VSTACK(arr, split),
IF(SUM(timer) = r, IFNA(discard, ""), Text2Columns(discard, timer))
)
)``````

# Re: Textsplit selecting a range.

As variant, if on Beta

``=REGEXEXTRACT(B20:B32, {"\d*","(?<=\s).*"} )``

with some assumptions regarding the input

# Re: Textsplit selecting a range.

I shall take a look on my computer with Insiders. This was one of the first things I tried with Regex a few weeks ago but I didn't pursue it further.
1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

# Re: Textsplit selecting a range.

``=DROP(REDUCE("",SEQUENCE(ROWS(A3:A15)),LAMBDA(x,y,IFNA(VSTACK(x,TEXTSPLIT(INDEX(A3:A15,y,1)," ")),""))),1)``

An alternative could be the above formula.

TEXTSPLIT can't spill down and to the right at the same time. Spilling down and to the right at the same time would mean to return an array of arrays which in general isn't possible. Actually the array of arrays returns an error with e.g. LAMBDA and BYROW. For =TEXTSPLIT(A3:A15," ") it means that the intended result can't be returned.

In the example in the screenshot =TEXTSPLIT(A3," ") would create a horizontal array with 2 columns and =TEXTSPLIT(A11," ") would create a horizontal array with 8 columns.

If we tried

=BYROW(A3:A15,LAMBDA(x,TEXTSPLIT(x," ")))

it would result in a CALC! error because of nested arrays (or array of arrays). The LAMBDA and BYROW would need to spill down and to the right at the same time which isn't possible with LAMBDA and BYROW (but with LAMBDA and REDUCE).