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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!