SUBSTR calculation help

[OndinaSt]

I have a custom field that is manually entered with a URL. I am trying to make a calculated field on the same form which will return everything after the last / in the URL field. While I did get SUBSTR({field},#) to work, I would prefer to say that the starting point is what comes after the ".com/" instead of the character # the "/" is in the URL. I've tried using SUBSTR({field},"website.com/") which returns the full URL. Can the SUBSTR calc only use a numerical starting point? Are there other expressions I need to bring in? Wasn't able to find much in the help site or community so hoping for some guidance from the experts. Thanks!

[KathyLe1]

Hi Ondina, If I understand what you’re looking for, you want to take the following text: http://www.workfront.com/level1/level2/doc.aspx And return: level1/level2/doc.aspx Is this correct? If so, you might try this instead: RIGHT({field},LEN({field})-(SEARCH(".com/",{field})+5)) The tricky bit will be if you have different domain suffixes (.edu, .org, etc.). In that case, you might need to throw things in a nested IF statement to capture the ones you’re most commonly running into. Hopefully that helps! Kathy

[OndinaSt]

Hi Kathy, thank you so much, that works beautifully! I'm learning expressions and intuitively I see what you did but can't put it into layman's terms. For future need, do I have this correct? It is taking the right length of the string before .com/ and subtracting it in addition to subtracting the 5 characters from .com/...? So it's seeing the .com/ as a "starting point" of what to return (or cut off from the beginning of the URL)?

[KathyLe1]

I’m so glad that helped! And I think you have it! Since we need to tell the RIGHT function, the exact number of characters we want to return from the right of {field}, we first need to figure out the entire length of {field} (since that could be anything). Then we subtract out everything prior to SEARCH (which is the number SEARCH gives us) + the search string (“.com/”) itself. Good luck learning expressions! They really are kind of fun once you get the hang of them.

[OndinaSt]

great, thanks again! Now to see where else I can have fun with these tidbits ;)

[OndinaSt]

Hi Kathy, So, as I was reviewing the data before I switched the field on the form last night, I noticed that the URL some users entered included search string characters at the end (ie ?sr458=q&....). Apparently it depends on how they navigate to the URL in question. I now need to account for those characters which means I need to get to the characters between the ".com/" and the first "?". My understanding is that the length I want is 15 characters although I still need to accommodate for that not always being the case. I've tried including another SEARCH for the ?, along with CONCAT, SUBSTR, LEFT in various places but I'm struggling with nested expressions. For now I've created a 2nd field to return the first 15 characters of your original calculation, so I have a workaround for the time being. Thank you again for your time and am glad to take this offline at your convenience.

[KathyLe1]

Hi Ondina, With that being the case, I think your best bet is to return to SUBSTR. The following calculation checks to see if there is a “?” in {field} and if so, it will return just the portion between the “.com/” and the “?”. If not, it will just return anything after the “.com/”. IF(SEARCH("?",{field})>-1,SUBSTR({field},SEARCH(".com/",{field})+5,SEARCH("?",{field})),RIGHT({field},LEN({field})-(SEARCH(".com/",{field})+5))) In the back of my head, I have a nagging feeling there may be some cases where this will do something unexpected, but hopefully it works for the bulk of what you’re going to be dealing with. Good luck! :-) Kathy

[OndinaSt]

Hi Kathy, brilliant! Confirmed with testing and will implement this weekend. Many thanks for your time and knowledge sharing!

[KathyLe1]

Glad it worked! I'm happy to help.