Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Trim a string with special characters

LMJ-SFI
5 - Atom

I'm trying to clean up data where the users tend to enter the data in different formats. The string is a digital object identifier for publications and may sometimes be entered as 10.1234/abc, sometimes as http://doi.org/10.1234.abc and a number of other variations. 

 

I wanted to use the TRIM function to correct the entries (TrimLeft([DOI Corrected],"http://doi")) but it doesn't behave as i expect i.e. it's not just removing the text "http://doi" from the beginning of the string, it sometimes just removes the first letter, sometimes just "http://". I presume it's got something to do with either the colon or the forward slashes but I can't find info on what these characters might be doing to my formula.

 

Apologies if this is a really basic question. Any help welcome!

4 REPLIES 4
Paul-Evans
9 - Comet

TrimLeft is looking for any of the characters in your second argument. So for example, the p in "http:" is also removing the p from "pending".

Paul-Evans
9 - Comet

Given the variability of things you want to remove or keep, regex might be your best option. Here's an example to get you started with your data.

LMJ-SFI
5 - Atom

oh dear. i completely misunderstood how trimleft worked! I thought i could specify a particulary string. That explains it. Thanks so much!

LMJ-SFI
5 - Atom

That's brilliant! Thanks so much.

Labels