Check text string for range of text entries

Microsoft Excel 2003 (full product)
August 2, 2010 at 12:36:31
Specs: Windows XP
Hello - here is what I'm trying to do:

- Cell A1 on sheet 1 contains a text string (ie. "I need help with an excel function")

Range A1:B10 on sheet 2 looks like:
1 The 10
2 Car 20
3 Help 30
4 Dog 40

etc ....

I need a formula in sheet 1 that looks at the string in A1 and compares it to the range in Sheet 2. If one of the words in Sheet 2 are contained in the Sheet 1 text string, return the numerical value to the right of the matching word in Sheet 2.

So, in my example above, the formula would return "30".

Alternatively, if this is not possible, I could work with the formula returning the matching word only (ie. "help" in the example above)


See More: Check text string for range of text entries

August 2, 2010 at 16:20:30
Returning the "30" is not the problem, finding the word "help" via a formula is.

There is no built-in Excel function that can break the sentence into individual words and compare them to a list.

To do that, you need a macro. Based on your example, this ought to work:

Option Explicit
Sub FindWords()
Dim theString, nxtLetter, newWord As String
Dim nxtChr As Integer
Dim c As Range
'Load theString with the string from A1
 theString = Sheets(1).Range("A1")
'Loop through the string looking for a space
  For nxtChr = 1 To Len(theString)
'Check the character
   nxtLetter = Mid(theString, nxtChr, 1)
'If it's not a space, then append it to the newWord
    If nxtLetter <> " " Then
     newWord = newWord & nxtLetter
'If it is a space, then we have a complete word to search for
      With Sheets(2).Range("A1:A10")
       Set c = .Find(newWord, lookat:=xlWhole)
'If the word is found in A1:A10, return the associated value
        If Not c Is Nothing Then
         MsgBox c.Offset(0, 1)
'Uncomment Exit Sub if only one word needs to be found
'As written, multiple words can be found.
        'Exit Sub
        End If
      End With
'Clear the newWord variable
       newWord = ""
'Increment the character counter
       nxtChr = nxtChr + 1
'Start builing the next newWord
       GoTo BuildNextWord
    End If
End Sub

Report •

August 3, 2010 at 08:45:54
Derby - thanks for your reply. Actually was able to create a custom function that did the trick ...

Report •

August 3, 2010 at 09:14:44
Would you care to post that Custom Function?

We're always looking for alternative solutions that we may be able to use in the future.

Report •

Related Solutions

August 10, 2010 at 11:22:32
I have a similar problem and I'd love to know that custom function too!! But I'll try the macro, thank you Derby! :)

Report •

Ask Question