Nesting RIGHT and LEFT functions to parse characters from a string

Julie Hinton

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!!

Stefan

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

Stefan

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