Multiple Find Function Generating Error

[DangerKennedy]

Hey, I am looking for a function I can use to find the words "MS", "IV", "FM", and "XR" from a cell of data. I currently have this formula that words for just "XR" below and returns : XR0551AJ to my cell in A1.

=TRIM(LEFT(SUBSTITUTE(MID(Q1,FIND("XR",Q1),LEN(Q1))," ",REPT(" ",100)),100))

How can I manipulate this formula this include other find criteria mentioned above in case it has something like Geisinger Bayer Contract Media MS5018 Tiered Pricing?

Example Data in my Q column:

Geisinger Bayer Contrast Media XR0551AJ Tiered Pricing

Geisinger Bayer Contrast Media MS5018 Tiered Pricing

Geisinger Bayer Contrast Media IV200111 Tiered Pricing

 

[Michael M]

Maybe this way, drag down as required

=IF(OR(COUNTIF(Q1, "*XR*"),COUNTIF(Q1, "*MS*"),COUNTIF(Q1, "*IV*")),Q1, "No")

 

[DangerKennedy]

Maybe this way, drag down as required

=IF(OR(COUNTIF(Q1, "*XR*"),COUNTIF(Q1, "*MS*"),COUNTIF(Q1, "*IV*")),Q1, "No")

This is close! My results are:

Kimberly-Clark Sanitary Paper FM0393 Tiered Pricing I am looking for the result: FM0393

 

[Michael M]

I'm sure there will be a much shorter version.....

=IF(OR(COUNTIF(Q2, "*XR*"),COUNTIF(Q2, "*MS*"),COUNTIF(Q2, "*IV*")),MID(Q2,SEARCH("Media",Q2)+6,SEARCH("Tiered",Q2)-SEARCH("Media",Q2)-6), "Nothing")

 

[Fluff]

Another option

=LET(a,TEXTSPLIT(Q2," "),FILTER(a,(LEFT(a,2)="xr")+(LEFT(a,2)="ms")+(LEFT(a,2)="IV")+(LEFT(a,2)="fm"),""))