Find unique abbreviation in file name

Hi,

I would like to know is it possible - if I paste a file name in cell :-

A1 "J000379_Existing_S-001-02" then it will show in cell B1 "Schematic" - it's pick up the unique abbreviation "S-"

or

A1 "J000379_Existing_L-001-02" then it will show in cell B1 "Site Layout" - it's pick up the unique abbreviation "L-"

and so on

General Arrangements A-
Civil C-
Site Layout Drawings L-
Schematic Diagrams S-
Wiring Diagrams W-
Multicore Cable Diagrams M-
Block Cable Diagrams B-
Documents D-
Vendor V-

Thank you

Please post a sample sheet ( see yellow banner) with some further examples of strings to lookup or are they all the same differing only by S- or L- or...?

Hi,

Sorry the unique abbreviation ("L-")is the only reference - the rest of the file name description will relate to the drawing, this could be any length,

Cheers

You can adapt Glenn's example, e.g.:

=LOOKUP(9.99E+307,SEARCH("_"&$K$1:$K$9,$A1),$L$1:$L$9)
copied down

where A1 holds filename, K1:K9 holds your hyphen variants (e.g. A-, L-) , and L1:L9 holds the value you wish to return when variant found

Hi,

That's smashing does the job, is it possible to get rid of the "#N/A" ?

Cheers

yes, you can wrap the above within an outer IFERROR

=IFERROR(LOOKUP(....),"")

Works a treat thank you.