Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Wildcard character in an "If" statement?

Yes, you can use a wildcard character in an "IF" statement in Excel. The wildcard character you can use is the *, which represents any number of characters.

To use the wildcard character in an "IF" statement, you can use the COUNTIF function. Here's an example:
  1. Code:
    =IF(COUNTIF(A:A,"1B*")0,"Certain Answer","")

In this example, "A:A" is the column where you want to search for the data containing "1B*". The asterisk (*) after "1B" represents any number of characters that may follow "1B".

The COUNTIF function counts the number of cells in the range that meet the specified criteria. If the count is greater than 0, the "IF" statement returns "Certain Answer". If the count is 0, the "IF" statement returns an empty string ("").
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.





  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.










  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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:D$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.






  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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:D$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.






  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Wildcard character in an "If" statement?

From your formula it looks like column R is supposed to numbers and you're
using NOT(ISLANK()) to make sure empty cells aren't counted.

Try it like this. Normally entered:

=SUMPRODUCT(--('Input form'!C$4:C$549=$G$3),--(ISNUMBER(SEARCH($H$3,'Input
form'!D$4:D$549))),
--('Input form'!R$4:R$549<""),--('Input form'!R$4:R$549<=$A126))

--
Biff
Microsoft Excel MVP


"tiredazdaddy" wrote in message
...
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:D$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.








  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default Wildcard character in an "If" statement?

Tiredazdaddy,

Excel has explanations on wildcard functions in text strings in the help
files. As I was looking at your post, I realized that my post was close to
two years ago now.

My particular post had to do with looking to have what I called a dual duty
test. I.e., if I had one string, then I wanted it to perform a particular
function. If it had another string, it'd perform another. While I solved my
particular problem, I haven't used it but perhaps once or twice since then.

The way your string is configured, I think what you want is not a sum, with
an if equation, but one that's called Sumproduct.
It allows for up to 30 criteria (in pre-Excel2007), and up to 255 criteria
in Excel2007.
I also use that to perform count functions where I have more than one
criteria.

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

would become

=sumproduct(('Input Form'!C$4:C$549=$G$3)*('Input
Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*('Input
Form'!R$4:R$549))

each dataset within paren's is an array for the first three elements in your
equation.

The last set appears to return a boolean, or true/false response.

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

What I'm seeing here is the following-- and I could be missing something, so
if I am, please forgive me.
You're looking to compare a specific cell against a range to find that
element- $G$3
AND you want to compare $H$3 to a second range.
AND you want to compare $A126 to be = the final range.
WHERE the range R4:$549 is not blank.
where all these properties are true, you want to return true-1, or if not
true, you return a false- 0.
For all of your true values, you want them summed.
Is my understanding correct?

If I'm understanding what you're asking, the sumproduct would work. But, as
you mentioned, it only works for exact matches. I haven't figured out
Sumproduct looks at all occurences within the range, that match G3, H3, and
<=A126. Each of those return either true or false-- 1 for true, 0 for false.
The last range is your sum range. R6:R549.
So, the response will look something like-
1*1*1*1*value = value.
0*1*1*1*value = 0-- all variations with 0 will return 0.
It then adds all values, and gives the total of all the values.

One of the problems that I've found with linking to other worksheets-- in
your case, Input Form-- is that the cells can have different data types. When
this occurs, it will never return true, or 1 for those arrays.
I've found that with my data type troubles, there are data-type converters.
Your equation would then look like:

=sumproduct(('Input Form'!C$4:C$549&""=$G$3&"")*('Input
Form'!D$4:D$549&""=$H$3&"")*('Input Form'!R$4:R$549<=$A126)*('Input
Form'!R$4:R$549))

Note that the convertors are an ampersand with two double quotes &"".

where, the first two arrays are some text, or general datatype. Since you're
using <= with the 3rd array, I'll assume you have a numeric datatype there.
and the last array is your sum range- also numeric.

you can read more on sumproduct in the excel help file as well.

My next question to you would be-- did you look at my answer from David B?

HTH.


"tiredazdaddy" wrote:

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:D$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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 11:54 PM


All times are GMT +1. The time now is 12:47 AM.

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

About Us

"It's about Microsoft Excel"