UDF for finding text in cell from a list of text

**tom.hogan** · 01-25-2011, 03:15 PM

I need a UDF to determine if a cell has any of several words from a list and return one phrase if true and another if false

Example:
A1: The big red apple tree
A2: The big apple tree

B1: Color
B2:Blue
B3:Red
B4: Yellow
etc.

so for A1 it would look for any of the words in column B and return "COLOR" since Red is in A1 and in column b

For A2 it would return "NO COLOR", since none of the words in column B are in the string in A2.

Thanks in advance,

Tom

**Leith Ross** · 01-25-2011, 06:35 PM

Hello Tom,

Welcome to the Forum!

This UDF will return "Color" or "No Color" based on the entries in the named range "Colors".

Function HasColor(ByVal Phrase As String)

  Dim Item As Variant
  Dim SearchValues As Variant
    
    SearchValues = Split(Phrase, " ")
    
    For Each Item In SearchValues
      On Error Resume Next
        WorksheetFunction.VLookup Item, Range("Colors"), 1, False
        If Err = 0 Then HasColor = "Color": Exit Function
      Err.Clear
    Next Item
        
    HasColor = "No Color"
    
End Function

**tom.hogan** · 01-25-2011, 07:53 PM

Leith,
Awesome, thanks for the quick reply, it works perfectly!

One more question, how would you make this more generic, i.e. allow entries into the formula for the range and the responses. Say we call the formula ISWORD() with the following entries: search cell, range of desired words, response if true, response if false

Thanks again,

Tom

**Leith Ross** · 01-25-2011, 09:10 PM

Hello Tom,

Here is the updated macro. If you don't specify a true/false response then the macro returns True or False.

Function IsWord(SearchString As String, SearchValues As Range, Optional TrueResponse, Optional FalseResponse)

  Dim Item As Variant
  Dim SearchItems As Variant
  
    If IsMissing(TrueResponse) Then TrueResponse = True
    If IsMissing(FalseResponse) Then FalseResponse = False
    
    SearchItems = Split(SearchString, " ")
    
    For Each Item In SearchItems
      On Error Resume Next
        WorksheetFunction.VLookup Item, SearchValues, 1, False
        If Err = 0 Then IsWord = TrueResponse: Exit Function
      Err.Clear
    Next Item
        
    IsWord = FalseResponse
    
End Function

**tom.hogan** · 01-25-2011, 11:12 PM

Leith,
Again awesome, it works great! Now if i could trouble you one last time. Is it possilbe to have a varible response if true based on a second column in the range? Let's call this one IFTEXT() with the following entries: search cell, range of desired words, column to look in if true (usually 2), response if false

Example
Range
Red Color
Tree Plant
Dog Animal

The big red house - would return "Color"
The tall tree - would return "Plant"
The large hairy green dog - would return "Animal"
The fast car - would return "response if false"

I know that a string with more than one of the words in the 1st column of the range would cause issues, but I can deal with that via naming conventions.

Thanx,

Tom

P.S. I don't see the scales or the "solved" icons, I want to be sure you get credit for your great answers, can you explain where I find them?

**Leith Ross** · 01-26-2011, 02:30 AM

Hello Tom,

The scales are on the post menu bar (the top of each post). See the attached picture for what it looks like.

Here is how to mark a post as "solved":

How to mark a thread Solved

Go to the first post
Click Edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No PrefIx.
Change to Solved
Click Save

As for this next macro, you are basically doing a VLookup within an If statement. This can be accomplished using formulas.