How to add character in the front, if condition is met on that same cell

[Emilie]

Hello

I have a column with series of number but I'd like to keep it as a string. Let's call this column "HS code".

I want a rule (maybe using Regex or Conditional statement) telling Alteryx to add a zero ("0") in front of my number only if the number starts with a 9.

Examples:

If HS code is 21069098497, then leave as is.

If HS Code is 9023000000, then populate 09023000000

Can you help me find the correct formula for this?

[Kenda · Accepted solution]

Try this: iif(left([HS code],1)="9","0"+[HS code],[HS code])

[nick_ceneviva · Accepted solution]

The equation that @Kenda used is correct but will only work if the [HS code] field is a string.  If it is coming from the source as a number, you will need to use the select tool to convert to a string before the formula tool with the if statement or use the ToString function in the formula tool.

[Emilie]

Thank you both for your help!

[N0SHEEN]

Hi Barnesk

I am in the same situation but I need to put condition on second digit of the cell( string), if second digit is zero and length is 5 then add zero at the end. what should be used instead of left ? 

[Kenda]

Hello @N0SHEEN

You could use an expression like this in your Formula tool:

iif(right(left([Field1],2),1)="0" && Length([Field1])=5,[Field1]+"0",[Field1])

if you still wanted to use the left/right functions. 

Otherwise, if you'd like to try Reg_Ex, see if this formula would work for your case:

iif(REGEX_Match([Field1],".0..."),[Field1]+"0",[Field1])

[N0SHEEN]

Perfect ! this is working for me. Thanks so much for the help :)