How do pull specific text from a string of text like a URL

mromaire

/(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

Carson Penticuff

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

Carson Penticuff

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?

mromaire

Yes they will always be known. Some variation of 4 and -9.

Carson Penticuff

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

mromaire

Fantastic! This works perfect. Thanks Carson!

Paul Newcome

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

mromaire

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.

Paul Newcome

That can only be done through third party apps such as Zapier (maybe) or through the API (definitely).