RIGHT and/or LEFT functions to parse characters from a string



I have a problem with parsing some characters from the URLs in one cell to another cell using the RIGHT and LEFT functions. I am not sure this is even the best way to do it, but it seems like I should be able to nest these in such a way that I get the result I need.


I have one column that has URLs, and within those URLs are ID#s that I want to pull out into another column. Unfortunately, some of the URLs place the ID# to the right of some text, and others place the ID# to the right of a different delimiter.


URL 1: lala.com/3hdiui8GH74l09dAAA. <- ID# is the first 15 characters (out of 18) to the right of ".com/"

URL 2: lala.com/cups/hgts?id=3hdiui8GH74l09d <- ID# is the 15 characters to the right of "id=" (or, last 15 characters)

The string could either have example URL1, or URL2 so I need to have a formula that tests one argument and returns the ID if true, or if false tests the second argument and returns the ID. I tried to run a LEFT function with a IF CONTAINS to test for the "id=" text, then RIGHT 15 but I am running into problems. Does anyone have any experience with this?


Best Answer


  • Julie Hinton

    @David Joyeuse that worked! I had to make one small adjustment (added "m/" and +2 to account for / found earlier in the URL) but this did exactly what I needed it to!

    =IF(FIND("=", [URL]@row)>0, RIGHT([URL]@row, 15), MID([URL]@row, FIND("m/", [URL]@row)+2, 15))

    I need to learn a lot more about MID and FIND, I am more used to using CONTAINS functions to work with problems like this.

    Thanks so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!