Solved

# can you use wildcards in an if formula excel 2003

Posted on 2011-03-06
356 Views
I would like to use the wildcard * in an IF formula.   If cell A contains the string IND I would like it to populate in cell D, if cell A contains MV I would like it to populate column D, if neither is found in Cell A then I want the cells to be blank.  Are wildcards allowed in IF statements?  If so, what is the syntax and cell formatting to make the attach file work.

expWildcard.xls
0
Question by:mossyback
• 4
• 2
• 2

LVL 24

Expert Comment

You can use find instead, see the attachment:

expWildcard-updated.xls
0

LVL 24

Accepted Solution

jimyX earned 250 total points
Better use search because it is not case sensitive.
0

LVL 50

Expert Comment

Do you just want the relevant number or the whole cell contents, e.g. what should be in C13 and D13?

If you want the whole cell contents in both then try this formula in C2 copied down and across and format to wrap text

=IF(ISNUMBER(SEARCH(C\$1,\$A2)),\$A2,"")

see attached

If you want to extract the number from each so that C13 is 1 and D13 is 2 then that's another formula, how long will the numbers be...only single digits or longer?

see attached for that initial option....

regards, barry
26866774.xls
0

LVL 50

Expert Comment

To extract just the numbers change to this formula

=IF(SEARCH(C\$1,\$A2&C\$1)<LEN(\$A2),MID(\$A2,SEARCH(C\$1,\$A2)-2,1)+0,"")

regards, barry
0

LVL 50

Expert Comment

This attachment illustrates that last formula......

barry
26866774v2.xls
0

Author Closing Comment

Thanks JimyX.  That did the trick!
0

Author Comment

barryhoudini  - Your solution is also interesting.  I might be able to use it on something else.  Good stuff!
0

LVL 50

Expert Comment

In this formula

=IF(ISERROR(IF(FIND("IND",A3)>0,A3,"")),"",IF(FIND("IND",A3)>0,A3,""))

The IF functions are really redundant because either the value "IND" is found in A3....in which case the IF is TRUE.....or it isn't in which case the IF is never evaluated because FIND returns an error, so you have an IF function in whch the FALSE can never be returned. My suggested formula simply checks whether SEARCH (or FIND) returns a number or not, which is all you need.

regards, barry
0