Replacing certain characters in a string

[armenti]

Hi. I'm trying to figure out how to replace all of the underscores between the numbers in this string with a period (.) and also replace all other underscores with a space ( ).  Any assistance will be appreciated.  Thanks.

[DataNath]

Hey @armenti you can use:

Replace([Factor], '_', ' ')

I can see a few cases there where this would leave you with large gaps of duplicate whitespace. If you want to get rid of that, you can use the Data Cleanse tool, ensuring the following configuration is ticked:

[armenti]

Thanks for the response, but that replaces all of the underscores with space.  I still need to have periods in between the numbers.

[DataNath · Accepted solution]

Apologies @armenti, didn't read through properly! How does this look instead? I used the following for my Formula (you would just replace [T] with [Factor]):

Replace(REGEX_replace([T], '_+(\d)','.$1'),'_',' ')

[armenti]

Thank you so much.  This worked perfectly.  Now I just have to figure out why.

[DataNath]

No problem @armenti, happy to give a little explainer! The expression is nested and so we’ll work from inside to outside, the first being the regex_replace() function:

RegEx is used to identify patterns in string fields and the function works on defining the following: ([Target field], ‘target pattern’, ‘replacement’).

Here you wanted to replace underscores between numbers with a (.) and so the pattern I used was ‘_+(\d)’.

The + here basically tells Alteryx, the pattern it should look for is one or more underscores, followed by a number, which is denoted by \d. Now the reason the number is bracketed is to make it a capture group. This means that when you define your replacement, you can reference this capture group and use it as part of the replacement string - rather than dropping it completely. Therefore, the ‘.$1’ replacement is telling Alteryx to replace every occurrence of x underscores followed by a number, with a . and the number it extracted from that instance of the pattern.

The outer expression - the simpler Replace() function - acts on the result of the inner regex_replace. As the regex replace has already taken place and all underscores between numbers have been replaced with (.), the only remaining underscores are those between letters. The replace function works in a similar way in that you define ([Target field], ‘target string’, ‘replacement’), and we can therefore just tell Alteryx to replace all remaining _ with a space ‘ ‘.

Hope this helps! If you’d like to learn a little more about RegEx then there’s plenty of great materials online - can be a very powerful tool if you’re dealing with strings containing regular patterns that you need to tackle!