CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
Rus Marcoderin
Rus Marcoderin

Reputation: 67

Removing unwanted numbers after delimiter in Excel

I have a table like this below:

Smartphone  Manufacturer    Region
iPhone 12   Apple   US
Galaxy S20  Samsung EUROPE:1233
        
Galaxy Note 10  Samsung EUROPE;345
Galaxy Note 9   Samsung EUROPE1234
Pixel   Google  US
        
Moto g3 Motorola    ASIA

Effectively, I want to remove everything that is not a valid letter in the region section. For example EUROPE:1233 is invalid and should instead be just EUROPE. I also dont want any numbers such as EUROPE1234 and instead just want EUROPE on its own. Is there a formula that I can use such that it automatically corrects all the rows in the Region in one go?

Also, is there a quick way to remove empty rows?

Any help will mean a lot!

Thanks in advance!

Upvotes: 1

Views: 94

Answers (1)

Swen
Swen

Reputation: 587

Use this formula

=QUERY({B1:C,ArrayFormula(IFERROR(REGEXEXTRACT(D1:D,"[[:alpha:]]+")))},"where Col1 is not null",1)

enter image description here

Upvotes: 1

Related Questions