Formula to extract certain text

[rachel06]

Hello

I'm trying to create a formula that will extract a text string from report names. What I'm needing to get out of the below is "REB000001". One issue I'm having is not all of those numbers have 9 characters. These file names should all be like this where the REB### is either going to be followed by a period or an underscore.

REB000001_Hospital System Inc_Calc Sheet_2023_Q3.xlsx
HOSPITAL SYSTEM INC_ REB000001_Rebate Summary.xlsx
max1539g_417517_HOSPITAL_SYSTEM_INC_ REB000001.xlsx
HOSPITAL_SYSTEM_INC_ REB000001_Remit.pdf

Thanks so much!!!

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	REB000001_Hospital System Inc_Calc Sheet_2023_Q3.xlsx	REB000001
3	HOSPITAL SYSTEM INC_ REB000001_Rebate Summary.xlsx	REB000001
4	max1539g_417517_HOSPITAL_SYSTEM_INC_ REB000001.xlsx	REB000001
5	HOSPITAL_SYSTEM_INC_ REB000001_Remit.pdf	REB000001
Data

Cell Formulas
Range	Formula
B2:B5	B2	=LET(a,TRIM(TEXTSPLIT(A2,{"_","."})),FILTER(a,EXACT("REB",LEFT(a,3))))

 

[Cubist]

One more option:

=TEXTBEFORE(MID(A2,FIND("REB",A2),LEN(A2)),{"_","."})

 

[Scott Huish]

The maximum length of a Windows filename is 255 characters, so you could drop the LEN function call and just use that.

=TEXTBEFORE(MID(A2,FIND("REB",A2),255),{"_","."})

 

[Cubist]

The maximum length of a Windows filename is 255 characters, so you could drop the LEN function call and just use that.

=TEXTBEFORE(MID(A2,FIND("REB",A2),255),{"_","."})

You're right.

I've realized you can also use TEXTAFTER.

="REB"&TEXTBEFORE(TEXTAFTER(A2,"REB"),{"_","."})

 

[rachel06]

Thank you all!! This is wonderful and way shorter than the find, mid, left, right monstrosity I was coming up with

 

[Fluff]

Glad we could help & thanks for the feedback.