regex - Remove everything except numbers and alphabets from a string using google sheet or excel formulas

Question

I have search but found python and related solutions.

I have a string like

"Hello 'how' are % you?"

which I want to convert to below after Remove everything except numbers and alphabets

Hello how are you

I am using Regexreplace as follows but now sure what should be the replacement or if its a right approach

=REGEXREPLACE(B2 , "([^A-Za-z0-9]+)")

The main thing i want to remove from the string are the stuff like " or strange symbols

can anyone help?

question from:https://stackoverflow.com/questions/65559524/remove-everything-except-numbers-and-alphabets-from-a-string-using-google-sheet

Answer 1

You can use:

=TRIM(REGEXREPLACE(B2,"[W_]+"," "))

Or, include the space in your character class:

=REGEXREPLACE(B2,"[W_ ]+"," "))

Where: W is short for [^A-Ba-b0-9_], so to include the underscore we added it to the character class.