Excel 2007 : Extracting data from a string in a cell

**nickelcell1** · 05-23-2011, 08:28 PM

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** · 05-23-2011, 09:09 PM

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

Svc_Cde   Years    Months
#B        MTM      MTM
#C        1 YR     12_Months
#D        2 YR     24_Months
#E        3 YR     36_Months

This regular formula returns the Years for cell A2

B2: =INDEX($F$2:$F$5,MATCH(1,INDEX(COUNTIF($A2,"*"&$E$2:$E$5&"*"),0),0))

This regular formula returns the Months for cell A2

C2: =INDEX($G$2:$G$5,MATCH(1,INDEX(COUNTIF($A2,"*"&$E$2:$E$5&"*"),0),0))

Copy those formulas down through Row_6

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

 
MTM      MTM
2 YR      24_Months
1 YR      12_Months
2 YR      24_Months
3 YR      36_Months

Is that something you can work with?

**Qualo_Jinn** · 06-01-2011, 04:45 PM

Originally Posted by Ron Coderre

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.