RIGHT and/or 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!!

David Joyeuse

Hi @Julie Hinton

Let's break this down in three separates formulas first.

1st case URL is this:

=MID([URL]@row, FIND("/", [URL]@row)+1, 15)

2nd case URL:

=RIGHT([URL]@row, 15)

3rd: Differentiate the type of the URL:

=IF(FIND("=", [URL]@row)>0, Case 2, Case 1)

Which gives us:

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

Hope it helped!

David Joyeuse

Hi @Julie Hinton

Let's break this down in three separates formulas first.

1st case URL is this:

=MID([URL]@row, FIND("/", [URL]@row)+1, 15)

2nd case URL:

=RIGHT([URL]@row, 15)

3rd: Differentiate the type of the URL:

=IF(FIND("=", [URL]@row)>0, Case 2, Case 1)

Which gives us:

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

Hope it helped!

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!