Cleaning strings in Alteryx TipTuesday #TipWeek

by Benedetta Tagliaferri

Welcome in Tip Tuesday of DS2 for our #TipWeek!

Today I am going to speak about strings in Alteryx. In particular this tip will help you to clean string fields in your dataset.

Last week we had a client project where I had to create an automated evaluation process for marketing campaigns. As a campaign label I had this to type of strings:

  1.  AW033_nameOfTheCampaign
  2. OBC436a nameOfTheCampaign

In order to have my ‘nameOfTheCampaign’ nice and clean in my field I had to make some transformations with the tool formula.



I had to create an IF THEN ELSE conditional formula. If the number of letters from the left would have been the same for each row, then I would have used the ‘TrimLeft(string,len)’ formula inside stings functions.

After many attempts I solved my problem with this formula:

IF Contains([delivery_label], ‘_’)
(FindString([delivery_label], ‘_’)+1))
(FindString([delivery_label], ‘ – ‘)+3))


I had some campaigns with an _ as a separation between the code and the name of campaigns and others with SPACE-SPACE ( – ).

So I wrote: IF contains inside delivery label (name of the campaigns) this symbol  ‘_’, then take the right part of the string. In order to specify the length that I want to take from the right (and because the length of the rows are all different, I wrote length of delivery label minus the code part (FindString of delivery_label at this point  ‘_’ and add 1 so it doesn’t take the _ character).

The formula continues with ELSE, but in this case we want SPACE-SPACE ( – ) so  I wrote +3 because we want to get rid of two spaces plus one character.




I hope this is useful!

See you tomorrow!


© 2022 The Information Lab Ltd. All rights reserved.