UDF for finding text in cell from a list of text

[tom.hogan]

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]

Hello Tom,

Welcome to the Forum!

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

Please Login or Register  to view this content.

[tom.hogan]

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]

Hello Tom,

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

Please Login or Register  to view this content.

[tom.hogan]

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]

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

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

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

[tom.hogan]

Leith,
VLookup is where i started. But it cannont parse a string, that is why I went down the UDF route. I was able to parse the string using text commands and put the separate words in multiple columns then do multiple vlookups (one per column) to get to my endresult, but it takes a lof of space and calc steps, thus my request for the above UDF. What i need is a VLOOKUP that can look in a string of text for a single word.

Anyway, thanks for all the help.

Regards,

Tom