Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HI.
I have 2 list of words. 1 is a list of top companies and another is a
list of all companies written in different ways. Now I want to find where the words in the first array of the top companies exist in the the second array of all companies of my contacts in my list and then mark them. Please let me know a formula which I can use. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HI.
Post some examples of the two lists.
But you could try this UDF from Rick Rothstein. Function ExactWordInString(Text As String, Word As String) As Boolean ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & _ UCase(Word) & "[!A-Z]*" End Function Company name in A1 Company name written differently in B1 but has common word with A1 i.e. Kellog's in A1 The Kellog's Company in B1 In C1 enter this............=ExactWordInString(B1,A1) Gord Dibben MS Excel MVP On Thu, 19 May 2011 09:45:46 -0700 (PDT), Sam wrote: I have 2 list of words. 1 is a list of top companies and another is a list of all companies written in different ways. Now I want to find where the words in the first array of the top companies exist in the the second array of all companies of my contacts in my list and then mark them. Please let me know a formula which I can use. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HI.
But you could try this UDF from Rick Rothstein.
Function ExactWordInString(Text As String, Word As String) As Boolean ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & _ UCase(Word) & "[!A-Z]*" End Function Company name in A1 Company name written differently in B1 but has common word with A1 i.e. Kellog's in A1 The Kellog's Company in B1 In C1 enter this............=ExactWordInString(B1,A1) Hey, you saved one of my UDFs... neat! In case you are interested, I developed an Excel formula equivalent to this UDF. For the cell contents as you have defined them above... =IF(ISNUMBER(SEARCH(A1,B1)),AND(NOT(ISNUMBER(FIND( UPPER(MID("="&B1&"=",SEARCH(A1,B1),1)),"ABCDEFGHIJ KLMNOPQRSTUVWXYZ"))),NOT(ISNUMBER(FIND(UPPER(MID(" ="&B1&"=",SEARCH(A1,B1)+LEN(A1)+1,1)),"ABCDEFGHIJK LMNOPQRSTUVWXYZ"))))) I developed this formula in response to the following blog article (where where the use of the A2 and B2 cell contents were reversed from how you proposed them in your response here)... http://www.get-digital-help.com/2009...-vba-in-excel/ Rick Rothstein (MVP - Excel) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HI.
Thanks Rick
Gord On Thu, 19 May 2011 13:42:52 -0400, "Rick Rothstein" wrote: But you could try this UDF from Rick Rothstein. Function ExactWordInString(Text As String, Word As String) As Boolean ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & _ UCase(Word) & "[!A-Z]*" End Function Company name in A1 Company name written differently in B1 but has common word with A1 i.e. Kellog's in A1 The Kellog's Company in B1 In C1 enter this............=ExactWordInString(B1,A1) Hey, you saved one of my UDFs... neat! In case you are interested, I developed an Excel formula equivalent to this UDF. For the cell contents as you have defined them above... =IF(ISNUMBER(SEARCH(A1,B1)),AND(NOT(ISNUMBER(FIND (UPPER(MID("="&B1&"=",SEARCH(A1,B1),1)),"ABCDEFGHI JKLMNOPQRSTUVWXYZ"))),NOT(ISNUMBER(FIND(UPPER(MID( "="&B1&"=",SEARCH(A1,B1)+LEN(A1)+1,1)),"ABCDEFGHIJ KLMNOPQRSTUVWXYZ"))))) I developed this formula in response to the following blog article (where where the use of the A2 and B2 cell contents were reversed from how you proposed them in your response here)... http://www.get-digital-help.com/2009...-vba-in-excel/ Rick Rothstein (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|