Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

benjamin_hoppin
5 - Atom
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
4 REPLIES 4
kane_glendenning
10 - Fireball
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
levell_x_dunn
10 - Fireball
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
5 - Atom

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
Alteryx Alumni (Retired)

Hi @Mir604 

 

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

 

TerryT_0-1572466058319.png

Labels