Google Sheet: How to ensure a string like "405,270" regarded as string but not number?-CodePudding

The data in my K8 cell is

405,270 | 450.00,300.00

other data in K column is like

270 | 300.00

I am trying to use

=index(split(K8," | ",0),1)

to get the value 405,270 so I can use another split to get value 405. However, the formula above returns 405270 as a number. How should I improve my formula?

CodePudding user response：

You can do it via REGEXEXTRACT:

=REGEXEXTRACT(K1,"(. )?\|")

And, if you need it as an array:

=ArrayFormula(IFNA(REGEXEXTRACT(K1:K,"(. )?\|")))

CodePudding user response：

In my case I substitute all "," with another symbol like "@"

So the final formula is like

=index(split(index(split(subsitute(K8,",","@")," | ",0),1),"@"),1)

CodePudding user response：

all you need is:

=SPLIT(K8; "| ,")