Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search a string in a cell for a list of 3 letter codes
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search a string in a cell for a list of 3 letter codes
SteveT wrote:
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search a string in a cell for a list of 3 letter codes
try =LOOKUP(,-FIND({"COU";"CUR";"DEC";"DEM"},A1),{"COUNTER CHANGE OUT";"CURB REPLACEMENT";"DECK/PATIO";"DEMOLITION"}) Regards Bosco "SteveT" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search a string in a cell for a list of 3 letter codes
On Thu, 6 Nov 2008 13:15:01 -0800, SteveT
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for string of characters in a cell | Excel Discussion (Misc queries) | |||
search if a string contains text matching items in a list | Excel Worksheet Functions | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
FIND / SEARCH text compare cell to string in 3rd cell | Excel Discussion (Misc queries) | |||
Using Vlookup in a string search of a cell | Excel Worksheet Functions |