Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 8th 06, 10:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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  
Old November 8th 06, 10:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old November 8th 06, 10:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old November 9th 06, 12:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old February 22nd 07, 09:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old February 22nd 07, 10:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old February 22nd 07, 10:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old February 22nd 07, 11:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old February 22nd 07, 11:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old November 20th 08, 07:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
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.








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


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017