Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
wild character in function?
Lets say I have few companies and each of company can have 100s 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 dont want to count them, I want to assign customer name to each customer. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
wild character in function?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
wild character in function?
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 "Greg" wrote in message ... Lets say I have few companies and each of company can have 100s 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 dont want to count them, I want to assign customer name to each customer. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
wild character in function?
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 "Greg" wrote: Lets say I have few companies and each of company can have 100s 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 dont want to count them, I want to assign customer name to each customer. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wild card with if logical function | Excel Worksheet Functions | |||
wild card with if logical function | New Users to Excel | |||
wild card with if logical function | New Users to Excel | |||
wild card with if logical function | Excel Worksheet Functions | |||
wild card with if logical function | Excel Worksheet Functions |