Remove special characters

[lledl]

I would like to do what "Data Cleanings" function does and so remove special characters from a field with the formula function.For instance: addaro'  becomes addaro, samuel$ becomes samuel

I know I can use-----> replace([field1],"$"," ") but it will only work for $ sign. I'm wondering If I can use multiple values all together such as $,() .....

Thank you

[MarqueeCrew]

Regex_Replace([Field],"[^\w\s]",'')

This will permit only {a-z,A-Z,0-9,'Space'}.  You can replace "[^\w\s]" with:

"[^a-zA-Z]" to remove anything that isn't a letter.

Does this solve your need?

[lledl]

This will only return A-Za-z right?, it won't return é inside of QUéBEC. Actually somebody else had a similar question as well, I checked the post but could not find the answer I was looking for.

Regex_Replace([Field],"[^\w\s]",'')..so this will return QUéBEC as QU BEC if I'm not mistaken

[MarqueeCrew]

@lledl,

I tested your question with the following code:

regex_replace([Field],"[^\sa-zA-Z]",'')

When [Field] has a value of: QUéBEC, the output is:  QUBEC.

I think that is what you are requesting.

Thanks,

Mark

[fomenter · Accepted solution]

I think you want to keep your non english character as well (in other words you only want to remove punctuations like ./- etc) Your best bet is to use replace function if you want to consider non english characters. The only drawback is that you will have to specific each character individually. For example: if no dot (.) is desired in output, insert dot as given below (same thing for the dash). I'm no expert on this but I thought this may give you some ideas..

Replace(Replace([F1],".",""),"-","")

[tom_montpool]

You might also want to look at this other Community post -- http://community.alteryx.com/t5/Elegantly-remove-all-ASCII-characters-outside-the-range-32-126

[skolluru78]

Hi ,

below is my string name "Contents" and the data in this string is like below. I need to write a reg expression to replace all the special character, emotions , @ .. etc.. can you please help me how can I solve this problem.

Contents - [FIELD NAME]
@Ian_Fraser but #xxx no Jail
The Pudsey branch of XXX have pay-in envelopes in Welsh... Why Welsh? Is there a substantial amount of Welsh speakers here?
@n1k17a87 Of course I can, you can either deposit it at a HSBC branch, a Post Office, but you would need to have a pre printed 1/3 ^JD
Dear @XX I'm leaving you. Gave you 20 years, but I've had enough. #goodbye 👋🏼
@beverly_road .@XXX you seem to have forgotten about one thing while the fat cats get rich - your customers. We can walk tho. And we will.

[sdodero]

Thank You very much !

[alexhenson]

Spoiler