RIGHT and/or LEFT functions to parse characters from a string
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
-
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!
Answers
-
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 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!