<< Back

ReplaceMultipleChars()

Hat tip to my former colleague Phil Mannering for this one.

In Alteryx there are are probably hundreds of ways to replace characters, for example the data cleanse tool, or via regex; but personally my most often used method is a simple replace() function, something like…

replace([Field],”,”,””)

In this function I am replacing commas with nothing.

If I want to remove something else in the same text, I do the following…

replace(replace([Field],”,”,””),”£”,””)

I am now replacing both the comma and the £ that exists in our text, so £4,540 would become 4540, which we can then convert into a number without any issues.

What Phil taught me, is the ReplaceChar() function that exists within the tool; this function allows you, even though it may not be obvious, multiple characters in a ‘single hit’.

The sytnax looks like…

ReplaceChar([Field], “y”,”z”)

The definition given by Alteryx is this…

Returns the string (x) after replacing each occurrence of the character (y) with the character(z). If the replacement character (z) is a string with more than one character, only the first one is used. If (z) is empty, each character (x) that matches any character in (y) is simply removed.

Now, what isn’t clear in this, is that actually, you can have multiple characters in the “y” piece, and it will replace each character within that set with “z”.

So my previously nested replace(replace()) statement, can now look something a bit nicer…

ReplaceChar([Sales],”£,”,””)

E Z.

Ben

Ben Moss

Leicester, UK

Leave a Reply

Your email address will not be published. Required fields are marked *