Excel - Wildcard in an IF statement

[RachieD]

Hi - is it possible to use Wildcards in an If statement?

For example, I want to say something like:

if A2 contains "Commit" then it is an "Inside" deal; else it is a "Pipeline" deal

but if I write the following

=IF(A2="*COMMIT*","INSIDE","PIPELINE")

and A2 looks like this:

A2
this is a commit deal

then the answer is Pipeline instead of Inside.

Can you please let me know what I'm doing wrong?

Thanks
RachieD

 

[combo]

You need to combine FIND and ISERROR functions instead.

combo

 

[RachieD]

Thanks for your response Combo - however, I'm afraid to say I have no idea how to write this... could you please give me an example?

Thanks a million!!

RachieD

 

[xlbo]

Have you tried looking in the help file for those functions ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RachieD]

Hi Geoff - yes I did look in the help file before I posted the question and when I received the answer from Combo, but I am still not sure how to do it....

Any help on this would really be appreciated...

Rgds

RachieD

 

[xlbo]

Would also suggest making sure it isn't case sensitive:

=IF(ISERROR(FIND("COMMIT",UPPER(A2))),"PIPELINE","INSIDE")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Find is case-sensitive.
Search is not case-sensitive.

So rewording Geoff's formula to be a bit shorter, you could use:
[tab]=IF(ISERROR(Search("COMMIT",A2)),"PIPELINE","INSIDE")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[RachieD]

Thank you very much Geoff, that worked brilliantly!!

Is it possible to use this in a nested IF statement too? I tried but got the message saying I'd used too many arguments...

Thanks again!!

RachieD

 

[xlbo]

Yes it is possible but if you are doing what I think you are doing (searching for lots of different strings in each cell), you are going to run into trouble

As a pointer, if you need help with a formula, it is generally a good idea to post what you have so we can see what you are trying to achieve

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RachieD]

Hi Geoff - thanks for your comments. This is what I am trying to do...

Column A is populated by text. I want to categorise the entries into the following: INSIDE, OUTSIDE, PIPELINE.

I want the formula to say:
if it says Commit then it is INSIDE,
if it says Inside then it's INSIDE,
if it says Outside then it's OUTSIDE,
everything else is PIPELINE.

Example:

A B
This is a committed deal INSIDE
Inside deal INSIDE
Outside chance OUTSIDE
Should close in August PIPELINE

Do you think it is possible to write something for this....?

Thanks again for all of your help and thanks to John (Anotherhiggins) too...!

Rgds
RachieD

 

[AnotherHiggins]

Ah. That changes the logic of what we're trying to do.

Geoff's use of IsError says, "If it isn't found". For this, I'd rather have it say, "If it IS found."

Find and Search both return numbers - the point in the cell where your searched-for-string is found. So if the string IS found, it will return a number, otherwise an error. I'll use IsNumber instead of the IsError function like so:

[COLOR=blue white]=IF(OR(ISNUMBER(SEARCH("COMMIT", A2)), ISNUMBER(SEARCH("INSIDE", A2))), "INSIDE", IF(ISNUMBER(SEARCH("OUTSIDE", A2)), "OUTSIDE", "PIPELINE"))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[RachieD]

Thank you so much....! That worked beautifully!!

I do apologise for steering you both in the wrong direction earlier on. I think I confused myself!!!

It should be against the law that both of you are so clever...!

Rgds

RachieD