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