How do pull specific text from a string of text like a URL
/(maps;id=default;@=41.13015856427597,-96.15103612629356,19//sp:jobs)
I want to pull the latitude and longitude coordinates from the text above into separate cells. I know how to do it in excel but don't know the SmartSheet equivalent.
Excel formula:
URL Link is in B6 cell.
Lat:
=TRIM(LEFT(SUBSTITUTE(MID(B6,FIND("4",B6),LEN(B6)),",",REPT(" ",100)),100))
Returns: 41.13015856427597
Long:
=TRIM(LEFT(SUBSTITUTE(MID(B6,FIND("-9",B6),LEN(B6)),",",REPT(" ",100)),100))
Returns: -96.15103612629356
Thanks
Matt
Best Answer
-
These should work. This is assuming you set them up as column formulas and that your URL links are in the [URL] column.
=MID([URL]@row, FIND("4",[URL]@row, 1), FIND(",", [URL]@row, 1) - FIND("4",[URL]@row, 1))
=MID([URL]@row, FIND("-9",[URL]@row, 1), FIND("!", SUBSTITUTE([URL]@row, ",", "!", 2), 1) - FIND("-9", [URL]@row, 1))
Answers
-
It looks like your Excel formulas are locating the lat/long based on the first digit. Will the first digit always be known so that you can use that, or do you need something that will work with any coordinates?
-
Yes they will always be known. Some variation of 4 and -9.
-
These should work. This is assuming you set them up as column formulas and that your URL links are in the [URL] column.
=MID([URL]@row, FIND("4",[URL]@row, 1), FIND(",", [URL]@row, 1) - FIND("4",[URL]@row, 1))
=MID([URL]@row, FIND("-9",[URL]@row, 1), FIND("!", SUBSTITUTE([URL]@row, ",", "!", 2), 1) - FIND("-9", [URL]@row, 1))
-
Fantastic! This works perfect. Thanks Carson!
-
You can also make it more dynamic if your lat and long start with different values. That way you don't have to worry about having anything hardcoded.
=MID(URL@row, FIND("=", URL@row) + 1, FIND(",", URL@row) - (FIND("=", URL@row) + 1))
and
MID(URL@row, FIND(",", URL@row) + 1, FIND("!", SUBSTITUTE(URL@row, ",", "!", 2)) - (FIND(",", URL@row) + 1))
-
That's great! Thanks Paul.
So I have around 400 hyperlinks (xfmr #s) that display a number in the cell instead of the link. Is there a way to reference the URL link for these cells and use it in this formula without the need to have the URL in a link all itself? This would save me from having to pull all the links out separately into the G/Connect column.
-
That can only be done through third party apps such as Zapier (maybe) or through the API (definitely).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!