How to add character in the front, if condition is met on that same cell
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Preparation
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Try this: iif(left([HS code],1)="9","0"+[HS code],[HS code])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you both for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Perfect ! this is working for me. Thanks so much for the help :)
