IF Statement for beginner

[Tommaso83]

Hi Everyone

Completely new here.

Trying to build my first workflow and missing some basics

I would need help for 3 formulas:

1) FORMULA n.1: if mobile number starts with +, then replace + with 00

2) FORMULA n.2: if company is "ABC" then create email address firstname.lastname@abc.co.uk

3) FORMULA n.3: if last name contains added msc, then remove msc (example: if last name is Smith msc, then keep only Smith)

Thank you for your help 😉

[jacob_kahn · Accepted solution]

1. IF StartsWith([Mobile Number],"+") THEN Replace([Mobile Number], "+","00") ELSE [Mobile Number] ENDIF

Make sure to replace [Mobile Number] with [Name of your field]

In Formula Tool - Create new field

2. IF [Company Name] = "ABC" THEN [First Name] +"."+ [Last Name]+"@abc.co.uk" ELSE "" ENDIF

3. TrimRight([Name],"msc")

Welcome to the Alteryx world! Let us know how we can help further.

J

[Kenda · Accepted solution]

Hey @Tommaso83 

Welcome to the Community and Alteryx!

Here are my recommended expressions to use in a Formula tool for each of your problems:

1. 

iif(Substring([mobile number],0,1)="+",replace([mobile number],"+","00"),[mobile number])

 - This uses an if statement that says if the first character of the mobile number field (string field) is a plus sign, replace that with "00", otherwise keep the mobile number as-is.

2.  

iif([company]="ABC", [first name] + "." + [last name] + "@" + [company] + ".co.uk", null())

 - This again uses an if statement to say if the company equals "ABC", then string together that email address, otherwise that field will be null.

3. 

Replace([last name], " misc", "")

 - I did not use an if statement for this one, but you certainly could. This expression simply looks for the string " misc" within the last name field and replaces it with the empty string. 

Hope this helps!

[Tommaso83]

Thank you so much

For Formula 2, what is the expression if I have multiple scenario such as

IF [Company Name] = "ABC" THEN [First Name] +"."+ [Last Name]+"@abc.co.uk" and 

IF [Company Name] = "DEF" THEN [First Name] +"."+ [Last Name]+"@def.co.uk" ?

Again, I'm a beginner here 😉

Thanks

[jacob_kahn · Accepted solution]

Awesome question like this:

IF

[Company Name] = "ABC"

THEN

[First Name] +"."+ [Last Name]+"@abc.co.uk"

ELSE

IF

[Company Name] = "ABC"

THEN

[First Name] +"."+ [Last Name]+"@def.co.uk"

ELSE

""

ENDIF

ENDIF

Let me know if you have a problem with that!

[Tommaso83 · Accepted solution]

One last question, sorry

if company is "ABC" and contact is John Smith what's the formula to create email address jsmith@abc.com?

Thanks again 😉

[jacob_kahn · Accepted solution]

I would do something like this:

IF [Company] = "ABC" THEN Left([First Name], 1)+[Last Name] + "@abc.com" ELSE "" ENDIF

Let me know if there is anything else!

Appreciate the likes and acceptances 🙂

[Tommaso83]

Something else has occurred

What is the expression for multiple trimright?

TrimRight([Name],"msc") +

TrimRight([Name],"bsc").....

[jacob_kahn]

Not sure I understand your question - I apologize.

Can you clarify a little?

[Tommaso83]

Sure

Is some scenarios I want to remove Msc, in others Bsc and so forth so I'd like to know the expression for using trimright with more than 1 word. 

Hope it's clear 😉