# Solved: Excel: Wildcard Formula



## calador (Mar 3, 2008)

I have tried a number of arrangements to which i think might work - unfortunately didnt work.

Example:

A B
1 Art Support Dude [Formula]

2 Tech Support Guy [Formula]

what i want to do is come up with a formula which will pick up the word "support" . If i do it in the find function i.e. *support* it does pick that cell up.

If i were to insert that into a formula say:

If(A1="*support*", "This works", )

it somehow does not work with that arrangement. I think its just the syntax problem so i am hoping someone can point me in the right direction.

Also, if you can shed some light on the ~ wildcard. What does it actually do?

Thanks


----------



## slurpee55 (Oct 20, 2004)

I messed with this some but am not having any luck with the wildcards so far. However, to answer one thing, your formula:
If(A1="*support*", "This works", ) only meets 1/2 of the required options - you need another response if it is negative, such as
If(A1="*support*", "This works","This doesn't work" )


----------



## slurpee55 (Oct 20, 2004)

This will return a value of True is the word is in there, but gives you a #VALUE! if it isn't....
=IF(SEARCH("Support",A1,1)>0,"True","False")


----------



## Aj_old (Sep 24, 2007)

slurpee55 said:


> This will return a value of True is the word is in there, but gives you a #VALUE! if it isn't....
> =IF(SEARCH("Support",A1,1)>0,"True","False")


why not to use the "Iserror" function:

```
=IF(ISERROR(SEARCH("Support",A1,1)),"False","True")
```
or 

```
=IF(ISERROR(FIND("Support";A1));FALSE;TRUE)
```


----------



## calador (Mar 3, 2008)

Thanks.. that works.. but now trying to understand both the functions is giving me the headache. The examples given really isnt much help too. Especially the "Search" help.

I dont quite understand what you mean by start_num. "Start_num is the character number in within_text at which you want to start searching."

say for example i have this word "extinguish" the starting character is e followed by x, then t and so on. So if i were to put my start number as say 6, it would start counting from t onwards?


> =SEARCH("e",A2,6) Position of the first "e" in the first string above, starting at the sixth position (7)


Is that about right or am i totally on the wrong track?


----------



## Aj_old (Sep 24, 2007)

```
=SEARCH("e",A2,6)
```
will return the position of the first "e" in the string =Right(A2,Len(A2)-5), 
in your example it will return error because it will search for "e" in the string "guish", but if you'll replace "e" with "i" you'll get the result "8" its the position of the first "i" after the 5 th character in the initial string "extinguish"


----------



## slurpee55 (Oct 20, 2004)

calador, it appears you were reading the help file - however, the word that it has a search for is Statements, not extinguish, so in that case, =SEARCH("e",A2,6) would skip the first 6 letters (Statem) and find the next e, in position 7. If you just entered =SEARCH("e",A2), it would find the first "e" in position 5.
So in this case it looks for "Support" starting at the first letter (the final 1 in the formula is optional - 1 is the default).
And I just have never gotten the hang of ISERROR...


----------



## calador (Mar 3, 2008)

Thanks for the replies.. now i am just trying to expand the formula so that it searches multiple entries instead of one:
IF{
ISERR{
OR{
Search("Tech",A1,1)
Search("Guy",A1,1)
Search("Support",A1,1)
}
}

}

Logic behind it is, if there is either the word Tech, Guy or support, it would return "true" else "#Value", If there there is at least a True in there, the OR function will return a another TRUE and the ISERR will return a False statement and then i can put the output i want in the IF function.

I have not put this to the test yet but I am just wondering if there is an easier way to go around this.


----------



## OBP (Mar 8, 2005)

When you find the "Matching" data to the wild card search what do you do with it?
It is possible that a Macro may be a better way to do the searching.


----------



## calador (Mar 3, 2008)

To use macro you have to learn the language which i am not familiar with at all. Unless i am going to do lots of macroing in the future, i dont think it would be the best for me to learn a new language


----------



## slurpee55 (Oct 20, 2004)

Actually Calador, although I am no coder, one thing I have noticed in my attempts to learn VBA is how similar Excel formulas are to some of it. OBP could tell you better, but there are IF statements and OR and AND statements - all things you use in advanced formulas


----------



## Aj_old (Sep 24, 2007)

Hi Calador
If you wanna to search for more that one value then your formula should have such a structure:

```
=IF(AND(ISERR(SEARCH("Tech";A1;1));ISERR(SEARCH("Guy";A1;1));ISERR(SEARCH("Support";A1;1)));FALSE;TRUE)
```


----------



## slurpee55 (Oct 20, 2004)

calador, just in case you get an error message from AJ's formula, it is due to differences in countries - I use commas instead of colons (we run into this again, eh, AJ?) - so the formula would be

```
=IF(AND(ISERR(SEARCH("tech",A1,1)),ISERR(SEARCH("Guy",A1,1)),ISERR(SEARCH("Support",A1,1))),FALSE,TRUE)
```
Nice formula, though, AJ!


----------



## Aj_old (Sep 24, 2007)

slurpee55 said:


> calador, just in case you get an error message from AJ's formula, it is due to differences in countries - I use commas instead of colons (we run into this again, eh, AJ?) - so the formula would be
> 
> ```
> =IF(AND(ISERR(SEARCH("tech",A1,1)),ISERR(SEARCH("Guy",A1,1)),ISERR(SEARCH("Support",A1,1))),FALSE,TRUE)
> ...


Slupree I just copied the formula from my workbook where I tested it and forgot to change the ";" to ","!
So my mistake, again!


----------



## slurpee55 (Oct 20, 2004)

No mistake as far as I am concerned - just a different language leads to a different use of the keyboard....I'm just glad you are here!


----------



## Aj_old (Sep 24, 2007)

Thanks!


----------



## calador (Mar 3, 2008)

As far as the wildcard question is concerned, i think everyone here have been so helpful that we went abit off track. Nonetheless, the original question was solved earlier in this post. I guess if i have anymore questions, ill post a new thread or do you think i should just continue it here and minimize the number of threads i start?


----------



## Aj_old (Sep 24, 2007)

calador said:


> As far as the wildcard question is concerned, i think everyone here have been so helpful that we went abit off track. Nonetheless, the original question was solved earlier in this post. I guess if i have anymore questions, ill post a new thread or do you think i should just continue it here and minimize the number of threads i start?


I think you should start a new thread!


----------

