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?
Solved! Go to Solution.
Try this: iif(left([HS code],1)="9","0"+[HS code],[HS code])
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.
Thank you both for your help!
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 ?
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])
Perfect ! this is working for me. Thanks so much for the help :)