how to do an if-then statement and replace a portion of a string?

jbradshaw

So I have a huge mess of an email cleaning script that i have decided to clean up and make nice.  It currently uses a bunch of separate Replace Formulas to change common email mistakes to either null or the correct email.  So something like
1 - Replace([EMAIL], "._COM", ".COM")
2 - Replace([EMAIL], "._NET", ".NET")
3 - Replace([EMAIL], "DONOTCONTACT", Null())
The above will take the portion of the email address that is jacked and make it correct.

What I want to do is write this in one big statement....
if EMAIL contains ._com then ._com become .com
or
if EMAIL contains ._net then ._net becomes .net
and so on and so forth

This should allow me to addres a ton of the fat-fingered email addresses and leave the more crazy things to be addressed manually.  The main issue now is my script is terribly inefficient because I have no real logic built into it, as i encounter enough weird stuff I find a way to address it and add it in the tool, it's time for it to be more intuitive and I'd like it to be in one window so I can see everything, or at least a lot of, what it is doing to this field.

One additional thing i can't figure out is how to get the logic to only change the portion of the field that I designate, so if the email is "john@gmail._com", how can i get it to change "._com" to ".com", leaving "john@" alone.  this works with all of the individual replaces, but i cant figure out how to incorporate that into one big statement to make it easier to work in later

Ned

One trick you can do is to reference the same field in a formula tool multiple times.  This will allow you to have each of your expressions run in sequence without having to use an IF statement at all.

Or you can chain them together with IF statements in a single formula.  It might look like this:

if findstring([EMAIL], "_com")>=0 then
 Replace([EMAIL], "_com", ".com")
elseif findstring([EMAIL], "_org")>=0 then
 Replace([EMAIL], "_org", ".org")
elseif findstring([EMAIL], "_DONOTCONTACT")>=0 then
 Null()
else
 [EMAIL]
endif

Finally, you might look into the Find/Replace tool which will allow you to do a table lookup of things you want to replace.  See the help for it:

http://help.alteryx.com/current/index.htm#FindReplace.htm