SOLVED

Excel: How to strip out all non-alphanumerical characters excluding spaces.

Copper Contributor

Hi 

I need to create an Excel formula that will strip out all non-alphanumerical characters excluding spaces. 

 

Example: Before formula:
This message, that is sent to you, “Mr Dog 1” : it’s said with care?!

 

After formula:

This message that is sent to you Mr Dog 1 its said with care

 

(This is just one example. My full spreadsheet has over 500 different ones, all with different text).

 

This is what I have so far:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"?",""),",",""),"'",""),CHAR(34),CHAR(34)&CHAR(34))

 

However, it doesn't seem to be able to remove the smart quotes (i.e. curly quotes)

 

Any ideas? Thanks!

 

 

2 Replies
best response confirmed by big_smile (Copper Contributor)
Solution

@big_smile 

The following will keep a-z, A-Z, 0-9 and spaces.

It will not keep letters with accents such as é or ü.

 

=LET(chars, MID(A1, SEQUENCE(LEN(A1)), 1), keep, IF((chars=" ")+(chars>="0")*(chars<="9")+(chars>="A")*(chars<="Z")+(chars>="a")*(chars<="z"), chars, ""), CONCAT(keep))

=WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~[^\w\s]~~" & A2)

=WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~[^\w\s]~~" & A2)

1 best response

Accepted Solutions
best response confirmed by big_smile (Copper Contributor)
Solution

@big_smile 

The following will keep a-z, A-Z, 0-9 and spaces.

It will not keep letters with accents such as é or ü.

 

=LET(chars, MID(A1, SEQUENCE(LEN(A1)), 1), keep, IF((chars=" ")+(chars>="0")*(chars<="9")+(chars>="A")*(chars<="Z")+(chars>="a")*(chars<="z"), chars, ""), CONCAT(keep))

View solution in original post