Here's one way:

If you only have a few names to lookup and the name is *always* at the
beginning of the string:

Assume the company names a Acme, Blah, Tidy and Red.

=IF(LEFT(A1,4)="Acme","Acme",IF(LEFT(A1,4)="Blah", "Blah",IF(LEFT(A1,4)="Tidy","Tidy",IF(LEFT(A1,3)=" Red","Red",""))))

Or this one (doesn't matter where the name is located within the string):

=LOOKUP(2,1/(ISNUMBER(SEARCH({"Acme","Blah","Tidy","Red"},A1)) ),{"Acme","Blah","Tidy","Red"})

If you have more than a few names then list those names in a range of cells
like F1:F20, then:

=LOOKUP(2,1/(ISNUMBER(SEARCH(F$1:F$20,A1))),F$1:F$20)

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Lets say I have few companies and each of company can have 100's
different
products. Products are defined as string and string can be a combinations
of
text and numbers (acme5), special characters (Acme_100, acme.tools) What
is
the best function to find customer? I tried =IF(A1="acm*","Acme",0), but
returned value is always false. What is the best way, function to select
specific customer? I don't want to count them, I want to assign customer
name to each customer. Thank you.