Extract text from the middle of a string

[Aronora06]

I have been bending my brain backwards trying to figure out how I can pull out a bit of text from the middle of a string like this one. The text that gets pulled out is a unique ID that will be used to search a different table to return a value. That's part 2 of this effort, but part one is just getting the text out of the middle...

Here is the full string, and the underlined/bold part is what I would need to pull out to be used in the search. Every entry in the column from this particular table is formatted similarly as it get's concatenated together in another database.

Course Name (blahblahblah) // START DATE: DD-MMM-YYYY // CRSE #: X3OZR14N3 00AA // CLASS NAME: 2022010 X3OZR14N3 00AA

[JOHNN]

You can use the function like mid([text_string],find("#:",[text_string])+2,find("CLASS NAME",[text_string])-4)

[Hani_Ibrahim]

You can use

TEXT(SPLIT("START DATE: DD-MMM-YYYY // CRSE #X3OZR14N300AA // CLASS NAME: 2022010 X3OZR14N3 00AA","#"))

but you will MUST remove any spaces or ":" between the "#" and the text you want to extract.

However, i wrapped  the (SPLIT) expression with (TEXT) to facilitate your next step since the split expression result is a list type.

hope that helps

[Steve]

ANY(SPLIT(INDEX(SPLIT([text], "CRSE #: "), 2), " // "))

[SkrOYC]

@Steve Would this count as a generated list or a constructed one? It's a little hard to tell form me, so I always use Index() instead of Any()

[Steve]

ANY(...) and INDEX(..., 1) are equivalent.