Using a IF not statement or something in a ReplaceChar

[SurajC21]

Hi All, 

I am trying to replace all characters in a column of data that are not alphanumerical or a select set of punctuation. I am not sure of all the other characters in the data currently in the column or that be added in the future, therefore I thought to add an if not in a ReplaceChar to try to replace any characters outside of my list of approved list of characters with a space. Below is what I used:

ReplaceChar([Search term], if ! "1,2,3,4,5,6,7,8,9,0,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,X,Y,Z, ., !, ,,?,&,-", " ")

When trying this I received a parse error for a malformed if statement. 

I am not sure if this a) possible or b) if there is a better way to go about it. But if anyone can help me know how I can go about in fixing this error and getting the replacechar to work as I intend or recommend a better way to go about this, that would be much appreciated. 

[Luke_C · Accepted solution]

Hi @SurajC21 

Try a regex_replace function:

REGEX_Replace([Field1], '[^a-zA-Z0-9\s.?!,&-]', '')

This removes any non-alphanumeric characters (a-zA-Z0-9) as well as the punctuation you noted (\s.?!,&-)

[apathetichell · Accepted solution]

@Luke_C  I think the special characters all need "\" first...

regex_replace([field1],![\w\s\.\?\!,\$-\&]+,"") should work