Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

can you use wildcards in an if formula excel 2003

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
mossyback
Asked:
mossyback
  • 4
  • 2
  • 2
1 Solution
 
jimyXCommented:
You can use find instead, see the attachment:

expWildcard-updated.xls
0
 
jimyXCommented:
Better use search because it is not case sensitive.
0
 
barry houdiniCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
barry houdiniCommented:
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
 
barry houdiniCommented:
This attachment illustrates that last formula......

barry
26866774v2.xls
0
 
mossybackAuthor Commented:
Thanks JimyX.  That did the trick!
0
 
mossybackAuthor Commented:
barryhoudini  - Your solution is also interesting.  I might be able to use it on something else.  Good stuff!
0
 
barry houdiniCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now