Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

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

Highlighted
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
Highlighted
Alteryx Certified Partner
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
Highlighted
9 - Comet
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
Highlighted
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

Highlighted
Alteryx Community Team
Alteryx Community Team

Hi @Mir604 

 

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

 

TerryT_0-1572466058319.png

Labels