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


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.


=TRIM(LEFT(SUBSTITUTE(MID(B6,FIND("4",B6),LEN(B6)),",",REPT(" ",100)),100))

Returns: 41.13015856427597


=TRIM(LEFT(SUBSTITUTE(MID(B6,FIND("-9",B6),LEN(B6)),",",REPT(" ",100)),100))

Returns: -96.15103612629356



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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!