Tags: certain, character, column, contains, example, excel, microsoft, msdn, return, software, statement, wildcard

Wildcard character in an "If" statement?

On Microsoft » Microsoft Excel

12,004 words with 8 Comments; publish: Fri, 23 May 2008 18:33:00 GMT; (30693.75, « »)

Can I use a wildcard character in an "IF" statement? For example, if any of

the data in a column contains "1B*", I want to return a certain answer. I

tried typing it in that way, and it was rejected. Couldn't find the answer

in Excel help.

Does anyone have the answer?

All Comments

Leave a comment...

  • 8 Comments
    • You can't directly use wildcards in an IF formula. You could use something

      like this:

      =IF(COUNTIF(A1:A10,"1B*"),value_if_true,value_if_false)

      Biff

      "ttretta" <ttretta.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:12A02010-9A70-4121-B343-8C1BDA9E8DF3.excel.todaysummary.com.microsoft.com...

      > Can I use a wildcard character in an "IF" statement? For example, if any

      > of

      > the data in a column contains "1B*", I want to return a certain answer. I

      > tried typing it in that way, and it was rejected. Couldn't find the

      > answer

      > in Excel help.

      > Does anyone have the answer?

      #1; Fri, 23 May 2008 18:35:00 GMT
    • Your best option is probably something like

      =IF(LEFT(A1,2)="1B",2,3)

      "ttretta" wrote:

      > Can I use a wildcard character in an "IF" statement? For example, if any of

      > the data in a column contains "1B*", I want to return a certain answer. I

      > tried typing it in that way, and it was rejected. Couldn't find the answer

      > in Excel help.

      > Does anyone have the answer?

      #2; Fri, 23 May 2008 18:36:00 GMT
    • Try this:

      =IF(ISNUMBER(SEARCH("1B",A1)),"found","not found")

      "ttretta" wrote:

      > Can I use a wildcard character in an "IF" statement? For example, if any of

      > the data in a column contains "1B*", I want to return a certain answer. I

      > tried typing it in that way, and it was rejected. Couldn't find the answer

      > in Excel help.

      > Does anyone have the answer?

      #3; Fri, 23 May 2008 18:37:00 GMT
    • I want to do what seems to be a similar IF function.

      Here is my version.

      =IF(a1="*stringname*",b1*1/2,b1*3/4)

      My goal is to look in a cell which contains a phrase. The phrase itself

      varies {the cell contents could be a name of a person, with (word) following

      it}, but the particular component that I'm seeking either shows up as (word),

      or as (word1). Eg., cell contents being within the dbl quote marks: "Dave

      Johnson (word)", or "Danny Thomas (word1)"

      Where "word" could be anything.

      I've tried already, and it <always> returns a false value-- b1*3/4.

      1- can I do this?

      2- what would I need to do in order to make it work?

      I've also tried the tilda, and question mark. Neither of those are working.

      If however, I just have it look in a cell with a single character, the

      equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd work

      well too if I had just a single word in the cell, as opposed to a number of

      them. For some reason I just can't get it to work with longer elements, where

      I want to locate a single word within a string of 5 or six words.

      If I'm unable to do this, what variation would I need to accomplish this?

      Thank you.

      #4; Fri, 23 May 2008 18:38:00 GMT
    • Here's one way:

      =IF(COUNTIF(A1,"*stringname*"),B1*0.5,B1*0.75)

      Or

      C1 = stringname

      =IF(COUNTIF(A1,"*"&C1&"*"),B1*0.5,B1*0.75)

      Biff

      SteveDB1" <SteveDB1.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:44655D9E-0204-429F-8C5B-78EB45F2EB65.excel.todaysummary.com.microsoft.com...

      >I want to do what seems to be a similar IF function.

      > Here is my version.

      > =IF(a1="*stringname*",b1*1/2,b1*3/4)

      > My goal is to look in a cell which contains a phrase. The phrase itself

      > varies {the cell contents could be a name of a person, with (word)

      > following

      > it}, but the particular component that I'm seeking either shows up as

      > (word),

      > or as (word1). Eg., cell contents being within the dbl quote marks: "Dave

      > Johnson (word)", or "Danny Thomas (word1)"

      > Where "word" could be anything.

      > I've tried already, and it <always> returns a false value-- b1*3/4.

      > 1- can I do this?

      > 2- what would I need to do in order to make it work?

      > I've also tried the tilda, and question mark. Neither of those are

      > working.

      > If however, I just have it look in a cell with a single character, the

      > equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd work

      > well too if I had just a single word in the cell, as opposed to a number

      > of

      > them. For some reason I just can't get it to work with longer elements,

      > where

      > I want to locate a single word within a string of 5 or six words.

      > If I'm unable to do this, what variation would I need to accomplish this?

      > Thank you.

      >

      >

      #5; Fri, 23 May 2008 18:39:00 GMT
    • I can't see the message to which you are replying, Steve, but as far as your

      question is concerned, try

      =IF(ISNUMBER(SEARCH("stringname",A1)),B1*1/2,B$1*3/4) or

      =IF(ISNUMBER(FIND("stringname",A1)),B1*1/2,B$1*3/4)

      FIND() is case-sensitive, SEARCH() isn not.

      --

      David Biddulph

      "SteveDB1" <SteveDB1.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:44655D9E-0204-429F-8C5B-78EB45F2EB65.excel.todaysummary.com.microsoft.com...

      >I want to do what seems to be a similar IF function.

      > Here is my version.

      > =IF(a1="*stringname*",b1*1/2,b1*3/4)

      > My goal is to look in a cell which contains a phrase. The phrase itself

      > varies {the cell contents could be a name of a person, with (word)

      > following

      > it}, but the particular component that I'm seeking either shows up as

      > (word),

      > or as (word1). Eg., cell contents being within the dbl quote marks: "Dave

      > Johnson (word)", or "Danny Thomas (word1)"

      > Where "word" could be anything.

      > I've tried already, and it <always> returns a false value-- b1*3/4.

      > 1- can I do this?

      > 2- what would I need to do in order to make it work?

      > I've also tried the tilda, and question mark. Neither of those are

      > working.

      > If however, I just have it look in a cell with a single character, the

      > equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd work

      > well too if I had just a single word in the cell, as opposed to a number

      > of

      > them. For some reason I just can't get it to work with longer elements,

      > where

      > I want to locate a single word within a string of 5 or six words.

      > If I'm unable to do this, what variation would I need to accomplish this?

      > Thank you.

      >

      >

      #6; Fri, 23 May 2008 18:40:00 GMT
    • David.

      Thank you.

      It works like a charm.

      Any items that I should be aware of as for not working? Or is that too large

      an answer...?

      Again... Thank you!

      "David Biddulph" wrote:

      > I can't see the message to which you are replying, Steve, but as far as your

      > question is concerned, try

      > =IF(ISNUMBER(SEARCH("stringname",A1)),B1*1/2,B$1*3/4) or

      > =IF(ISNUMBER(FIND("stringname",A1)),B1*1/2,B$1*3/4)

      > FIND() is case-sensitive, SEARCH() isn not.

      > --

      > David Biddulph

      > "SteveDB1" <SteveDB1.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:44655D9E-0204-429F-8C5B-78EB45F2EB65.excel.todaysummary.com.microsoft.com...

      > >I want to do what seems to be a similar IF function.

      > > Here is my version.

      > > =IF(a1="*stringname*",b1*1/2,b1*3/4)

      > > My goal is to look in a cell which contains a phrase. The phrase itself

      > > varies {the cell contents could be a name of a person, with (word)

      > > following

      > > it}, but the particular component that I'm seeking either shows up as

      > > (word),

      > > or as (word1). Eg., cell contents being within the dbl quote marks: "Dave

      > > Johnson (word)", or "Danny Thomas (word1)"

      > > Where "word" could be anything.

      > > I've tried already, and it <always> returns a false value-- b1*3/4.

      > > 1- can I do this?

      > > 2- what would I need to do in order to make it work?

      > > I've also tried the tilda, and question mark. Neither of those are

      > > working.

      > > If however, I just have it look in a cell with a single character, the

      > > equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd work

      > > well too if I had just a single word in the cell, as opposed to a number

      > > of

      > > them. For some reason I just can't get it to work with longer elements,

      > > where

      > > I want to locate a single word within a string of 5 or six words.

      > > If I'm unable to do this, what variation would I need to accomplish this?

      > > Thank you.

      > >

      > >

      > >

      >

      >

      #7; Fri, 23 May 2008 18:41:00 GMT
    • It could get tripped up when situations like this might arise:

      Looking for John:

      Johnson = a match

      John's = a match

      Johnston = a match

      Littlejohn = a match

      Either formula will trip on these, David's or the one I offered.

      One way to limit most of the problems is to pad the lookup value with a

      space on each end. But even this will not work on these type of situations:

      Looking for John:

      "John"

      John's

      John?

      John:

      John!

      John.

      Biff

      "SteveDB1" <SteveDB1.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:B6FC730D-149F-42BE-94CE-FDEAFFFE60AC.excel.todaysummary.com.microsoft.com...

      > David.

      > Thank you.

      > It works like a charm.

      > Any items that I should be aware of as for not working? Or is that too

      > large

      > an answer...?

      > Again... Thank you!

      > "David Biddulph" wrote:

      >> I can't see the message to which you are replying, Steve, but as far as

      >> your

      >> question is concerned, try

      >> =IF(ISNUMBER(SEARCH("stringname",A1)),B1*1/2,B$1*3/4) or

      >> =IF(ISNUMBER(FIND("stringname",A1)),B1*1/2,B$1*3/4)

      >> FIND() is case-sensitive, SEARCH() isn not.

      >> --

      >> David Biddulph

      >> "SteveDB1" <SteveDB1.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:44655D9E-0204-429F-8C5B-78EB45F2EB65.excel.todaysummary.com.microsoft.com...

      >> >I want to do what seems to be a similar IF function.

      >> > Here is my version.

      >> > =IF(a1="*stringname*",b1*1/2,b1*3/4)

      >> > My goal is to look in a cell which contains a phrase. The phrase itself

      >> > varies {the cell contents could be a name of a person, with (word)

      >> > following

      >> > it}, but the particular component that I'm seeking either shows up as

      >> > (word),

      >> > or as (word1). Eg., cell contents being within the dbl quote marks:

      >> > "Dave

      >> > Johnson (word)", or "Danny Thomas (word1)"

      >> > Where "word" could be anything.

      >> > I've tried already, and it <always> returns a false value-- b1*3/4.

      >> > 1- can I do this?

      >> > 2- what would I need to do in order to make it work?

      >> > I've also tried the tilda, and question mark. Neither of those are

      >> > working.

      >> > If however, I just have it look in a cell with a single character, the

      >> > equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd

      >> > work

      >> > well too if I had just a single word in the cell, as opposed to a

      >> > number

      >> > of

      >> > them. For some reason I just can't get it to work with longer elements,

      >> > where

      >> > I want to locate a single word within a string of 5 or six words.

      >> > If I'm unable to do this, what variation would I need to accomplish

      >> > this?

      >> > Thank you.

      >> >

      >> >

      >> >

      >>

      #8; Fri, 23 May 2008 18:42:00 GMT