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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!