Trim a string with special characters

[LMJ-SFI]

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!

[Paul-Evans · Accepted solution]

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 · Accepted solution]

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]

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

[LMJ-SFI]

That's brilliant! Thanks so much.