Regex replace and split using Google SHeets formula

Question

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]","")

Left() and right(), or mid() using find() for the first space. — Solar Mike
– Solar Mike, Commented Jun 13, 2021 at 6:44

anubhava · Accepted Answer · 2021-06-13 07:35:21Z

1

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.

answered Jun 13, 2021 at 6:44

anubhava

790k67 gold badges603 silver badges671 bronze badges

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

Comments

Harun24hr · Accepted Answer · 2021-06-13 06:54:03Z

0

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))

answered Jun 13, 2021 at 6:54

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges