Excel 2007 : Extracting data from a string in a cell

[nickelcell1]

I am trying to extract contract codes from a the string in the “SERVICE_CODES” column which contains numbers, punctuations and letters. I would like to return the data in the “Years” column of the service code table when the ct svc cde is found.
I have tried the following functions below with no luck. Can anyone assist me?
Thanks.

SERVICE_CODES
.T +( +/ *~ ;! ;= ;~ -~ >+ >? #B
*O #D ATC05 HSC07 HSC42 PM WQ 5
/H /K /0 /8 #C 5(
#D HSC42 WQ
#E HSC42 WQ

Ct Svc CdeYears Months
# B MTM MTM
# C 1 YR 12 Months
# D 2 YR 24 Months
# E 3 YR 36 Months

=LOOKUP(A2, {#B, #C, #D, #E}, {"MTM", "1Yr", "2Yr", "3Yr"}) – No luck

=--(ISNUMBER(SEARCH("#B",A3))) – No luck

=MID(A5,1,MIN(FIND({"#B","#C","#D","#E"},A1&"#B#C#D#E"))-1) – No luck

[Ron Coderre]

I think I've got something you can use...
With
Your posted data in A1:A6
and
your lookup list in E1:G5

Please Login or Register  to view this content.

This regular formula returns the Years for cell A2

Please Login or Register  to view this content.

This regular formula returns the Months for cell A2

Please Login or Register  to view this content.

Copy those formulas down through Row_6

With the test data, these are the results of those formulas

Please Login or Register  to view this content.

Is that something you can work with?

[nickelcell1]

Sorry Ron. Just got back from vacation. I will test this and get back to you to let you know if this works for me. Thanks for your patience.

[Qualo_Jinn]

I think I've got something you can use...
With
Your posted data in A1:A6
...

I'm not sure if that's what nickelcell was looking for but it's exactly what I needed. I was running into a similar issue of extracting data and Left(), Match(), and Find() weren't doing it for me but that use of Index works beautifully.

A tip of the hat, and scale, to you sir.

[nickelcell1]

Yes. It worked beautifully for me too.


Thank you.