HELP!
I am trying to format account numbers in alteryx.
My account numbers are currently displayed as such:
1234567890
I am trying to display them as:
1234-567-890
What tool/formula do I use to get this result?
I know the answer is right in front of me but I am so lost!
Thanks in advanced!
Solved! Go to Solution.
Hi,
You could try something like:
Left([AcctNbr],4) + "-" + SubString([AcctNbr],4,3) + "-" + Right([AcctNbr],3)
Hope that helps!
John
Here's one option. This will work if you make the account number field type a String.
Substring([acct_num],0,4)+'-'+Substring([acct_num],5,3)+'-'+Substring([acct_num],7,3)
Solutions already shared are valid, but in case you were still looking for some other options, here's one more...
REGEX_REPLACE(PADLEFT([Field1],10,'0'),'^(\d{4})(\d{3})(\d{3})$','$1-$2-$3')
This assumes that your account number only contains numerical chars. I first padleft the value with 0's if the value is less than 10 chars then use REGEX to create capture groups then output the groups with the '-' in between. If your account number can contain letters, replace '\d' with '\w' on any or all of the groups.
Best,
Jimmy