Nesting RIGHT and LEFT functions to parse characters from a string

Options

Hello!

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.


Problem:

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.


Example:

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?


Thanks!!

Best Answer

  • Stefan
    Stefan ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Julie Hinton,

    maybe similar to this?

    =IF(FIND("?id=", hours@row) <> 0, RIGHT(hours@row, 15), LEFT(RIGHT(hours@row, 18), 15))

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Julie Hinton,

    maybe similar to this?

    =IF(FIND("?id=", hours@row) <> 0, RIGHT(hours@row, 15), LEFT(RIGHT(hours@row, 18), 15))

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!