Formula, Parse, or RegEx?

[IJH34]

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!

[JohnJPS · Accepted solution]

Hi,

You could try something like:

Left([AcctNbr],4) + "-" + SubString([AcctNbr],4,3) + "-" + Right([AcctNbr],3)

Hope that helps!

John

[sjm]

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)

[jrgo]

@IJH34,

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