search for exact phrases in column of text strings

F

futurejock

New Member
Joined
May 31, 2009
Messages
20
Hi,

I may need just some nomenclature or switch

I have named a range of "find text" in a column.
I am searcing a "within text" column of cells, each with a text string.
I want to return match / no match for exact phrases, in exact order, NOT case sensitive.
I also want to continue getting a match for sincle words in the "find text" column (list).

Currently I get a match if a complete find text word is found, which is good since I don't want partial words or compound words in the text strings to match.

BUT if there are two or more words in my find text phrase,
I get a match even if one word is in the "within text" column.

For instance:

find text:
brown cow
yellow cow

within text:
the cow jumped over the moon
2) the brown cow is a funny cow
the cow is mellow, the one that is yellow
brown and yellow are the names of my cows
5) ****brown paddies line the cowyard
my son's hair has a brown cowlick.

I ONLY want to "match" for string example #2,
AND I want "No Match" for all the others.

Currently all of these except #5 match.

Also I have two ranges searching right now such as

CowsILike
Brown Cow
Yellow Cow

CowsIDon'tLike
Red Cow
Blue Cow

and the formula I am using is this:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsILike&" "," "&A4&" "),CowsILike)),IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsIDon'tLike&" "," "&A4&" "),CowsIDon'tLike)),"MAYBECowsI'llConsiderLiking","CowsIDOn'tLike"),"CowsILike")


Thanks,

Dan
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Re-check your "real" formula Dan, I couldn't see any reason why your sample wouldn't work, copiedit to a sheet to check and got your desired result, like for #2, consider for the rest.

I'm not able to reproduce the incorrect results you said you get, so can't see what the error could be.
 
Upvote 0
Hi thanks,

I will check again, but I think maybe my question got lost in the description I gave.

To answer you I will try it again / rebuild to test. Maybe it's confusing which result comes from which IF statement. Can you re-reun your test of my formula and change the result from the second IF from "CowsILIke" option to "CowsIOwn"?

Then, the problem I am struggling with is not which argument gives the a return, but rather that either argument give a YES/Match for any of those statements other than #2.

#2 is the only exact phrase "brown cow," meaning it has both words in the correct order.

Question: How to I ask the search to search for ONLY exact phrases in the exact order?

e.g. in a boolean search I would put quotes around

Thanks for your effort,

Dan

PS maybe we should re-write it with new strategy for my uber problem?
Which is simply how do I write 2 to 4 columns of "find text" arrays, which include phrases and singular words, which must be preserved or found exactly (definition= all letters of all words in correct order and all wordes in correct order, and disrespecting any case sensitivity (in either direction)), in a column of text strings?
 
Upvote 0
As far as I can see this is doing what you need, I've removed the lookups as they serve no purpose in this situation, that is only testing for a number returned from the search function, ISNUMBER() is a far more efficient way of doing this.
Excel Workbook
ABCDE
4the cow jumped over the moonNo Matchlist 1list 2
5the brown cow is a funny cowList 1Brown CowRed Cow
6the cow is mellow, the one that is yellowNo MatchYellow CowBlue Cow
7brown and yellow are the names of my cowsNo Match
8dirtbrown paddies line the cowyardNo Match
9my son's hair has a brown cowlick.No Match
Sheet
 
Upvote 0
Hi Jason,

This did nt work for me.

Maybe because I am in 2003?

Or maybe because I have my lists defined as dynamic. Maybe that's why I need LOOKUP(9.99999999999999E+307 ??

Anyway simplifying it as you showed is good, cause I see better how I can keep nesting rules to get a greater sort (less margin of error) if I keep adding lists (as additional variables).

D
 
Upvote 0
What formula are you using to define the dynamic ranges?

Not something I make a habit of using but I recall problems with them before, in theory if I use the same method as you have to define the ranges I should get the same errors.
 
Upvote 0
HI Jason,

thanks for your help. It's jts an idea the dynamic ranges. I don't thinkt it's the only variable. Something really weird is happening that the if true/if false locations seem to swap.

I'm not going to be able to mess with this timesaver anymore. and now have to work manually on my deadline.

anyway again, thanks.

D
 
Upvote 0
Just found it doesn't work properly, hence the reason why Aladin advised using lookup bignum http://www.mrexcel.com/forum/showthread.php?t=477848

Notice that where the match was found in list1, both the phrase in column A and the expression in list 1 are both in row 5, if something is moved so that they are on different rows of the worksheet then "No match"!

My error, the formula provided the desired result on the sample so I didn't test thoroughly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Search multiple exact strings in multiple columns
2
Replies
10
Views
317
Fluff
Fluff
D
  • Question
Issues with matches for Partial Text
Replies
1
Views
227
AhoyNC
A
J
  • Question
Finding one word in a string and outputting the word in the same row
Replies
3
Views
246
Peter_SSs
Peter_SSs
Sumeluar
  • Solved
Input box with selected text from cell
Replies
8
Views
134
Akuini
Akuini
S
  • Solved
Find Text in a cell
Replies
5
Views
121
Fluff
Fluff

Forum statistics

Threads
1,214,589
Messages
6,120,415
Members
448,960
Latest member
AKSMITH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top