Removing special characters from multiple fields

[Idyllic_Data_Geek]

I have a requirement to remove special characters from 5 fields. the list includes , ! ()# ' ;/ \ @ and replace them with space

the only 2 special characters that I need to retain are - &

Which function should I be using?

Thanks in advance

[Emil_Kos]

Hi @Idyllic_Data_Geek,

I am missing something obvious because I am quite sure that you can do that with one multi-field tool but I am not sure how to make it work. 

[Idyllic_Data_Geek]

Emil, thanks for your help. I will take a look and also wait for other experts to chime in!

[apathetichell]

@Emil_Kos  - FYI I think your formula doesn't account for special characters in regex (ie "!","(", and")" all need to be accessed via the exit character "\")...

@Idyllic_Data_Geek  this is working in the regex tool set to replace, don't copy unmatched text option:

([\d\w\-\&\s]+)

But I can't get it to work directly in the formula tool, because regex. If you don't get a better answer you can transpose and run that and then crosstab but obviously it's yucky.

This is an imperfect but a formula tool version:

regex_replace(tostring([_currentfield_]),"([\[\]\/\(\)\'\!\,;?\.]+)","$2") -  The problem is that I can't get the positive character set to work...hmm.

I'm happier with this one:

replace(tostring([_currentfield_]),regex_replace(tostring([_currentfield_]),"([\w\&\-\d]+)","$2"),"")

[T_Willins]

HI @Idyllic_Data_Geek,

Building on what @Emil_Kos built and the comments by @apathetichell.  I was able to get this to work in a single Multi-Field Formula tool using the formula 

REGEX_Replace([_CurrentField_], "[,!\(\)#';\\/@]", ' ')

This uses the exit (escape) to tell RegEx to find the special characters instead of using the special characters.  For more information on Perl Syntax used by RegEx formulas and tools, I recommend this guide on Community:  https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

[apathetichell]

@T_Willins- great job - I something really similar but with "$1" in the replacement and since the regex_replace formula version always includes unmatched text it ended up not working. I like the empty set in the replacement. My last entry did work - but that replace(x,regex_replace(...) is a messy format...