Convert a string into phone number format by inserting hyphens into field.

How would I convert a string of numbers 3037777777 into a classic phone number format: 303-777-7777. I know you can accomplish this in excel through the following function =REPLACE(REPLACE(A2,4,0,"-"),8,0,"-"). Is this something I can accomplish in Alteryx using RegEx or another expression I'm unaware of right now? Thanks for any help you can give me

Preparation

Accepted answers

kane_glendenning

Hi Benjamin,

The easiest way to do this would be
LEFT([Field],3) + "-" + RIGHT(LEFT([Field],6),3) + "-" + Right([Field],4)
The middle term could also be replaced with SUBSTRING([Field],3,3)

You could do it with RegEx, however I see that being more complicated.

Kane

All comments

kane_glendenning

levell_x_dunn

I agree with Kane,

formula is a lot easier, I prefer just using all substring instead of left/right

Substring([_CurrentField_],0,3)+'-'+Substring([_CurrentField_],3,3)
+'-'+Substring([_CurrentField_],6,4)

As with anything in Alteryx there are more than one way of accomplishing the same goal.

All depends on prerence

Levell

Mir604

Hello All,

My scenario is as follows:

I have a data set that has a data string (data set 1) such as contract number. I join it with another data set that has the same contract number but with " - " between certain characters in the data set. I need to be able to convert the data in data set 1 to add "-" between certain characters in order to be able to have the data fields matching each other. ie; data set 1 contract number converted to look like data set 2 contract numbers.

In data set 1, the contract appears as such: 1910081 or 19102521

In data set 2, the contract appears as such: 19-100-81 or 19-1025-21

(two examples of contracts)

What tools and or formulas can I use in order to convert the text in data set 1 to appear with same formatting "-" and all, same as data set 2

Please note, that in both cases, I need to add a "-" after the first two characters and also a "-" before the last two characters. The middle characters in the case of both examples (can be either 3 or 4 characters) must remain the same also.

Thanks in advance,

Mir

TerryT

Hi @Mir604

Here are two solutions (Left / Substring / Right) and Regex. I kinda like Regex. 🙂

mir604.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703