Pulling all text between 5th and 6th "_"

Options

Hello all, having a little trouble creating a forumla to pull this unique ID from a text string. I need to pull the text between the 5th and 6th delimiter ("_"). The only problem is these Unique ID's can vary from digit totals please see below: I would need "517184NEN1A3BD" extracted or the unique ID value between the 5th,6th "_"


NE_BOSTON_MCGEE MAZDA CLAREMONT_51718_YouTube_517184NEN1A3BD_CX-90_NOV_2023_CX-90

NE_BOSTON_MCGEE MAZDA CLAREMONT_51718_YouTube_517183XX1L3BDR_CX-30_NOV_2023_CX-30

NE_BALTIMORE_HERITAGE MAZDA TOWSON_51643_YouTube_516433XX1A3BD516434CCX1A3BD_CX-30_NOV_2023_CX-30

NE_BALTIMORE_HERITAGE MAZDA TOWSON_51643_YouTube_516434NEN1A3BD_CX-90_NOV_2023_CX-90

NE_BALTIMORE_HERITAGE MAZDA TOWSON_51643_YouTube_516433XX1A3BD516434CCX1A3BD_CX-30_NOV_2023_CX-30

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @ConnorForm

    Since Youtube is in all of your line items, I utilized that as a way to get the starting position. Then I took that result and parsed it a second time to get the ending position. See formula below.

    =LEFT(MID([Text String]@row, FIND("YouTube_", [Text String]@row, 1) + 9, 70), FIND("_", MID([Text String]@row, FIND("YouTube_", [Text String]@row, 1) + 9, 70), 1) - 1)

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @ConnorForm

    Since Youtube is in all of your line items, I utilized that as a way to get the starting position. Then I took that result and parsed it a second time to get the ending position. See formula below.

    =LEFT(MID([Text String]@row, FIND("YouTube_", [Text String]@row, 1) + 9, 70), FIND("_", MID([Text String]@row, FIND("YouTube_", [Text String]@row, 1) + 9, 70), 1) - 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!