Thanks for the feedback...I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Ron, you are the man! That did it perfectly. Now I just have to wait for it
to calculate.

"Ron Coderre" wrote:

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A1&" "," "&CATALOG&" ")))0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks for you effort Peo, but that does not work.
Ron's =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0 is the closest I have
gotten. Is there a way to use the EXACT function together with this?

"Peo Sjoblom" wrote:

How about

=(COUNTIF(CATALOG,A1)+COUNTIF(CATALOG,"* "&A1&" *")+COUNTIF(CATALOG,A1&"
*")+COUNTIF(CATALOG,"* "&A1))0

--
Regards,

Peo Sjoblom

Portland, Oregon




"micmed" wrote in message
...
What I need to know is if the part number in Col_A can or cannot be found
in
its exact form within the text of Array CATALOG. I do not want a positive
return if the part number text has anything immediately before or after.

Col_A CATALOG
Col_C
602 $602.35
No
456-T 456-T .......$25/ft
Yes
34-5678 Blue Arc Cover 34-5678
Yes
FL-975 Green 25-FL-975
No
3K-23G-85 $56.95 3K-23G-85 Plus Yes
WPL-1176 WPL- $1176
No

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
gives me every match regardless if it is exact or not.

I have 30K line items to search. This will really help me a lot.
Thanks,


"Ron Coderre" wrote:

While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought
to
what you must have and post back with the new rules. That way you can get
one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula
be
modified so that only exact matches are returned. For example A1 is
602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG
and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides