Pulling all text between 5th and 6th "_"
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!