Add punctuation in the V_Strings

maxi_reperger

Hi,

I'v started using Alteryx a week ago and was wondering if anybody could help me solve an issue I have.

I'm having a large data set as "###########" and need to have it as "#.#######.###" for technical purposes.

Is there any way I may do this?? I tried using the Text to Columns function but couldn't do it.

Thanks !

Regex

Parse

Best Practices

Transformation

Accepted answers

Philip

Hi @maxi_reperger

You can do this with either the RegEx Tool or a Formula Tool.

The Formula Tool expression would be:

REGEX_Replace([Field], "(\d{1})(\d{7})(\d{3})", "$1.$2.$3")

The \d means find a digit. The curly brackets tell how many numbers to find. The $1.$2.$3 is for each group in the RegEx expression contained in parentheses.

Let me know if you have any additional problems or questions.

All comments

NickC

Hello,

I would suggest using the parse functionality within the RegEx tool to separate the field into 3 columns

RegExOut1	RegExOut2	RegExOut3
#	#######	###

RegEx = (.)(.{6})(...)

Once you have these cols you can simply use the formula tool to combine them together with the additional punctuation added.

[RegExOut1]+'.'+[RegExOut2]+'.'+[RegExOut3]

Hope this helps,

I'm sure there are other ways to do this purely within the formula tool. So somebody feel free to post another solution!

Nick

Philip

Hi @maxi_reperger

You can do this with either the RegEx Tool or a Formula Tool.

The Formula Tool expression would be:

REGEX_Replace([Field], "(\d{1})(\d{7})(\d{3})", "$1.$2.$3")

The \d means find a digit. The curly brackets tell how many numbers to find. The $1.$2.$3 is for each group in the RegEx expression contained in parentheses.

Let me know if you have any additional problems or questions.

MarqueeCrew

Please try this formula:

Regex_replace([field],"(\d)(\d+)(\d{3})$",'$1.$2.$3')

Cheers,
Mark

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280