wild character in function?

[Guest]

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.

 

[T. Valko]

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 are: 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)

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure what ultimate formula you are looking for, but here is a
wild-card (and case insensitive) logical test that matches the one you
posted...

=IF(ISERR(SEARCH("acm*",A1)),0,"Acme")

Note that this reverses the location of what you had for the TRUE and FALSE
locations. We can flip that around if you insist (at the cost of another
function call)...

=IF(NOT(ISERR(SEARCH("acm*",A1))),"Acme",0)

Rick

 

[Guest]

Greg,

I think a UDF if what you need.

Copy this into a standard module:

Public Function ExtractCharsOnly(sStr1 As String)
Dim i As Long, sStr As String
Dim sChr As String
For i = 1 To Len(sStr1)
sChr = Mid(sStr1, i, 1)
If sChr Like "[A-Za-z]" Then
sStr = sStr & sChr
Else
Exit For
End If
Next
ExtractCharsOnly = sStr
End Function

Then with your data in column C in blank column ( say D) enter:
=ExtractCharOnly(C2)

Let me know if you like..

Jim