A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Wildcard character in an "If" statement?



 
 
Thread Tools Display Modes
  #1  
Old November 8th 06, 10:09 PM posted to microsoft.public.excel.worksheet.functions
ttretta
external usenet poster
 
Posts: 1
Default Wildcard character in an "If" statement?

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?
Ads
  #2  
Old November 8th 06, 10:36 PM posted to microsoft.public.excel.worksheet.functions
Biff
external usenet poster
 
Posts: 1,688
Default Wildcard character in an "If" statement?

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_f alse)

Biff

"ttretta" > wrote in message
...
> 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  
Old November 8th 06, 10:39 PM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
external usenet poster
 
Posts: 174
Default Wildcard character in an "If" statement?

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?

  #4  
Old November 9th 06, 12:19 AM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,718
Default Wildcard character in an "If" statement?

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?

  #5  
Old February 22nd 07, 09:54 PM posted to microsoft.public.excel.worksheet.functions
SteveDB1
external usenet poster
 
Posts: 414
Default Wildcard character in an "If" statement?

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  
Old February 22nd 07, 10:43 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default Wildcard character in an "If" statement?

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" > wrote in message
...
>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  
Old February 22nd 07, 10:50 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 618
Default Wildcard character in an "If" statement?

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" > wrote in message
...
>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  
Old February 22nd 07, 11:18 PM posted to microsoft.public.excel.worksheet.functions
SteveDB1
external usenet poster
 
Posts: 414
Default Wildcard character in an "If" statement?

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" > wrote in message
> ...
> >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.
> >
> >
> >

>
>
>

  #9  
Old February 22nd 07, 11:53 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default Wildcard character in an "If" statement?

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" > wrote in message
...
> 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" > wrote in message
>> ...
>> >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.
>> >
>> >
>> >

>>
>>
>>



  #10  
Old November 20th 08, 07:29 PM posted to microsoft.public.excel.worksheet.functions
tiredazdaddy
external usenet poster
 
Posts: 3
Default Wildcard character in an "If" statement?

Hi Steve,
I have something similar as I saw here also but is a little more complex. Do
you have any idea on how I can use wildcards to do something similar but with
it nested in with other variables?

I am trying to figure out how to search for a specific alphanumeric value
from a cell in a character string in another cell.

For instance, the below works fine if I only want to find an exact match for
the values within column “D” for that in cell “H3”, but I do not know how to
find the same value from “H3” if the values in column “D” contain a match
mixed in a character string.

Any help would be greatly appreciated!

=SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4$549=$H$3)*('Input
Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0))


"T. Valko" wrote:

> 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" > wrote in message
> ...
> >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.
> >
> >
> >

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wildcard Character in an Array Formula Scorpvin Excel Discussion (Misc queries) 2 September 30th 05 06:44 PM
How do you find and replace a Wildcard character in Excel? Wildcard Excel Discussion (Misc queries) 8 August 18th 05 01:18 AM
How do I use the wildcard character and the NOT function? KDA Excel Worksheet Functions 1 June 24th 05 04:36 PM
Wildcard character agenda9533 Excel Discussion (Misc queries) 1 May 11th 05 05:11 PM
Wildcard Character John Excel Worksheet Functions 7 November 30th 04 10:54 PM


All times are GMT +1. The time now is 04:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.