Split one column into two based on numbers and letters

user_15517892 0
Viewed 53

Basically, I am trying to remove all the numbers at the end of rows and store them in another column.

So I initially have something like this at the start:

Original List
Dr Pepper;1234
Pepsi 90s Drink001
Coca Cola
Red Bull R3 Drink
Mountain Dew;11
Gatorade
Fanta
Crush Soda456

I then want to split it so I get one list solely for the soft drink names and then one list solely for numbers at the end. So something like this:

Soft Drinks
Dr Pepper
Pepsi 90s Drink
Coca Cola
Red Bull R3 Drink
Mountain Dew
Gatorade
Fanta
Crush Soda

And then any numbers that appear at the list like:

Numbers
1234
1


11


456

I know you can use =ARRAYFORMULA(REGEXEXTRACT(A:A&"","[a-zA-Z\s]+")) but then it wouldnt get Red Bull R3 Drink for example and instead get Red Bull R

google-sheets-formula google-sheets
1 Answers

See if this works

=ArrayFormula(split(regexreplace(A2:A9, "(;?)(\d+)$","/$2"), "/"))

enter image description here

Explanation

See here for an explanation of the regex pattern.

user_38894230
Related