Prevent SPLIT from converting text to numbers in Google Sheets when used in an ARRAYFORMULA?

Question

I have rows of data like so:

a|b|1|c|d
a|b|1, 2|c|d
e|f||g|h

I am using BYROW to split them:

=BYROW(
    A2:A,
    LAMBDA(
        row,
        SPLIT(
            row,
            "|",
            ,
            FALSE
        )
    )
)

This works but then for cells that LOOK like numbers it converts to numbers. This breaks a QUERY formula I am trying to wrap around.

=QUERY(
    BYROW(
        A2:A,
        LAMBDA(
            row,
            SPLIT(
                row,
                "|",
                ,
                FALSE
            )
        )
    ),
    "SELECT * WHERE Col3 IS NOT NULL"
)

query does not like mixed data types in a single column (Query is ignoring string (non numeric) value) so the above function returns all the rows but does not return value in cells that don't have a number. So this is the output I get:


a	b	1	c	d
a	b		c	d

But the expected output is:


a	b	1	c	d
a	b	1, 2	c	d

I've concluded this is cause SPLIT converts to number. I can't use TO_TEXT becuase some columns have dates that I do want to convert.

player0 · Accepted Answer · 2024-05-16 13:36:21Z

1

instead buggy query, try non-invasive:

=LET(x, BYROW(A2:A, LAMBDA(r, SPLIT(r, "|",, ))), FILTER(x, INDEX(x,,3)<>""))

but if you really wish for query:

=INDEX(LET(x, QUERY(BYROW(SUBSTITUTE(A2:A, ",", "×"), 
 LAMBDA(r, SPLIT(r, "|",, )))&"", "where Col3 is not null", ), 
 IF((IFERROR(x*1)="")+(x=""), SUBSTITUTE(x, "×", ","), IF((x*1)>40000, TEXT(x, "dd/mm/yyyy"), x*1))))

edited May 16, 2024 at 13:36

answered May 16, 2024 at 12:57

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rockinfreakshow · Accepted Answer · 2024-05-15 21:06:30Z

1

Maybe alternatively you can just use filter() to not get into the query-mixed data type hindrance:

=let(Σ,byrow(A2:A,lambda(row,split(row,"|",,0))),
 filter(Σ,choosecols(Σ,3)<>""))

answered May 15, 2024 at 21:06

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Prevent SPLIT from converting text to numbers in Google Sheets when used in an ARRAYFORMULA?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related