Search code examples
regexstringgoogle-sheetsgoogle-sheets-formulaarray-formulas

My formula only keeps one word and not anything after spaces

I am basically trying to omitted all illegal characters and numbers in a soft drinks column. I currently have something like this:

Soft Drinks
Dr Pepper;1234
Pepsi369
Coca Cola
Red Bull
Mountain Dew;11
Gatorade
Fanta
Crush Soda456

Essentially I want something like this:

Soft Drinks
Dr Pepper
Pepsi
Coca Cola
Red Bull
Mountain Dew
Gatorade
Fanta
Crush Soda

I tried using this formula: =ARRAYFORMULA(REGEXEXTRACT(A1:A9&"", "[a-zA-Z]+")) but instead I am only getting the first word in the list please see below:

Soft
Dr
Pepsi
Coca
Red
Mountain
Gatorade
Fanta
Crush

Not sure where I gone wrong. I even tried fixing the regex like this and it still dont work: =ARRAYFORMULA(REGEXEXTRACT(A1:A9&"", "[a-zA-Z]+[a-zA-Z]+"))

Solution

  • This regex should work: [a-zA-Z\s]+

    =ARRAYFORMULA(REGEXEXTRACT(A1:A9&"", "[a-zA-Z\s]+"))

    enter image description here

    I only added \s so that space between words would also be included in your pattern. You can also use [a-zA-Z ]+, as using \s would match any whitespace character.