HI.

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

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.
 
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
 
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)
Click to expand...

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)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),NOT(ISNUMBER(FIND(UPPER(MID("="&B1&"=",SEARCH(A1,B1)+LEN(A1)+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

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/07/25/exact-word-in-string-without-using-vba-in-excel/

Rick Rothstein (MVP - Excel)
 
Thanks Rick

Gord

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)
Click to expand...

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)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),NOT(ISNUMBER(FIND(UPPER(MID("="&B1&"=",SEARCH(A1,B1)+LEN(A1)+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

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/07/25/exact-word-in-string-without-using-vba-in-excel/

Rick Rothstein (MVP - Excel)
Click to expand...
 
Back
Top