Search a string in a cell for a list of 3 letter codes

[SteveT]

I have a list of 3 letter codes (about 60 of them) that don't change. I
also have a bunch of strings exported from an application that contain one of
the 3 letter codes. All three letter codes are Caps and the exact location
of the code changes within each string. I was wondering if there was an easy
way to find out which code is in the string.

For example I have the list of codes in Sheet 2...
A B
COU COUNTER CHANGE OUT
CUR CURB REPLACEMENT
DEC DECK/PATIO
DEM DEMOLITION


And one of my strings in sheet 1 looks like...
A
13268 05/05/2008 5841 S LAGRANGEI OWNER: KEN HOOK 5 E AYERS HINSDALE,IL
60521 DEM *** 9,500.00 M & R DEMOLITION, INC. DEM B-3 400.00

Thanks in advance for any help
Steve

 

[Glenn]

I have a list of 3 letter codes (about 60 of them) that don't change. I
also have a bunch of strings exported from an application that contain one of
the 3 letter codes. All three letter codes are Caps and the exact location
of the code changes within each string. I was wondering if there was an easy
way to find out which code is in the string.

For example I have the list of codes in Sheet 2...
A B
COU COUNTER CHANGE OUT
CUR CURB REPLACEMENT
DEC DECK/PATIO
DEM DEMOLITION


And one of my strings in sheet 1 looks like...
A
13268 05/05/2008 5841 S LAGRANGEI OWNER: KEN HOOK 5 E AYERS HINSDALE,IL
60521 DEM *** 9,500.00 M & R DEMOLITION, INC. DEM B-3 400.00

Thanks in advance for any help
Steve

Maybe this will help. With your sting in A1 and COU, CUR, DEC & DEM in A2:A5,
put the following in B2 and copy down to B5:

=(LEN($A$1)-LEN(SUBSTITUTE($A$1," "&A2&" ","")))/(LEN(A2)+2)

....will tell you that "DEM" was found twice in your string, not counting the
occurrence in "DEMOLITION", which I assume you don't want counted.

 

[bosco_yip]

try

=LOOKUP(,-FIND({"COU";"CUR";"DEC";"DEM"},A1),{"COUNTER CHANGE OUT";"CURB
REPLACEMENT";"DECK/PATIO";"DEMOLITION"})

Regards
Bosco

 

[Ron Rosenfeld]

I have a list of 3 letter codes (about 60 of them) that don't change. I
also have a bunch of strings exported from an application that contain one of
the 3 letter codes. All three letter codes are Caps and the exact location
of the code changes within each string. I was wondering if there was an easy
way to find out which code is in the string.

For example I have the list of codes in Sheet 2...
A B
COU COUNTER CHANGE OUT
CUR CURB REPLACEMENT
DEC DECK/PATIO
DEM DEMOLITION


And one of my strings in sheet 1 looks like...
A
13268 05/05/2008 5841 S LAGRANGEI OWNER: KEN HOOK 5 E AYERS HINSDALE,IL
60521 DEM *** 9,500.00 M & R DEMOLITION, INC. DEM B-3 400.00

Thanks in advance for any help
Steve

Is there only going to be one code within the string?
Will the code ever be at the very beginning or end of the string?

How do you want to indicate that there is a code in the string?

NAME your code list, on Sheet2!A1:A60 or so: CodeList
NAME your code table, on Sheet2!A1:B60 or so: CodeTbl

If there will always be <space> before and after the code, and if there will
only be one unique code in each imported string,

Then with your string in A1:

Use this **array-entered** formula to return the code:

=INDEX(CodeList,MATCH(TRUE,ISNUMBER(FIND(" "&CodeList&" ",A1)),0))

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter>
Excel will place braces {...} around the formula.

To decode the return, you can use this:

Assume the above formula is in B1, then:

=VLOOKUP(B1,CodeTbl,2,FALSE)





--ron