Reputation: 3198

Regex replace and split using Google SHeets formula

I have a sheet where I need to split the numbers and text on single cell to 2 columns

Please note the data is in single cell and I need to split in 2 columns as shown in the image

The expected outout is

I have tried with =REGEXREPLACE(A796,"[0-9]","")

Upvotes: 1

Answers (2)

Reputation: 785256

You may use 2 formula in 2 different cells for this:

Formula #1:

=REGEXREPLACE(A4,"[[:blank:]].*","")

This finds first space and removes everything after that so that we have only starting numbers left in the result.

Formula #1:

=REGEXREPLACE(A4,"[0-9]+[[:blank:]]+","")

This matches starting 1+ digits followed by 1+ whitespace and removes it so that we only have text after starting numbers.

Upvotes: 1

Reputation: 36880

You can also try- B2

=TEXTJOIN(CHAR(10),False,INDEX(ArrayFormula(SPLIT(SUBSTITUTE(TRANSPOSE(SPLIT(A2,CHAR(10)))," ","@",1),"@")),,1))

C2=

=TEXTJOIN(CHAR(10),False,INDEX(ArrayFormula(SPLIT(SUBSTITUTE(TRANSPOSE(SPLIT(A2,CHAR(10)))," ","@",1),"@")),,2))

Upvotes: 0