Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Remove special characters

lledl
7 - Meteor

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

8 REPLIES 8
MarqueeCrew
20 - Arcturus
20 - Arcturus
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?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
lledl
7 - Meteor

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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
fomenter
8 - Asteroid

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
12 - Quasar
skolluru78
5 - Atom

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
6 - Meteoroid

Thank You very much !

alexhenson
8 - Asteroid
Spoiler

 

Labels