Wildcard in an If statement

Amileaux

Board Regular
Joined
Nov 3, 2002
Messages
110
Does a wildcard (* or ?) work in an if statement? For example in A1 I have the word "nextlevel" (wo the ""). In A2 I have: If(A1="nex*","Ok","No"). I have even tried If(A1="=nex*","ok","no"). In both instances I get "no", when I should be getting "ok". I figured out a work around using the left function - but still think the if statement should have worked.

A related question, when would I use "nex*" versus "=nex*"?

Thank you. Marie
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
The Array works perfectly, thanks Matty and Prabby.

@Matty
Yeah I probably should have started a new thread for this. I was searching in google and came across this thread--I didn't realize it was from '02 until after my post :LOL:

Thanks for the welcome, it's good to be a part of a new online community!
 
Upvote 0
Welcome to the Board...

You'd have been better opening a new thread for this, but try:

=IF(OR(ISNUMBER(SEARCH({"Nex","Big","Red"},A1))),"OK","NO")

Matty

Decided to pull this oldie but goodie up. Is there a way to replace the "Nex", "Big", "Red" with cell references instead of hard-coding? (Ex {A4, A5, A6}...doesn't seem to work)

Thanks.
 
Upvote 0
Try:

=IF(OR(ISNUMBER(SEARCH(B1:B3,A1))),"OK","NO")

Or using Barry's suggestion (one less Function call):

=IF(COUNT(SEARCH(B1:B3,A1)),"OK","No")

Both options need committing with CTRL+SHIFT+ENTER to work (due to their array nature), and where B1:B3 contains (in separate Cells) values such as Nex, Big or Red.

Hope this helps.

Matty
 
Upvote 0
Try:

=IF(OR(ISNUMBER(SEARCH(B1:B3,A1))),"OK","NO")

Or using Barry's suggestion (one less Function call):

=IF(COUNT(SEARCH(B1:B3,A1)),"OK","No")

Both options need committing with CTRL+SHIFT+ENTER to work (due to their array nature), and where B1:B3 contains (in separate Cells) values such as Nex, Big or Red.

Hope this helps.

Matty
Matty - best news all day. Thanks for the help.
 
Upvote 0
Good afternoon. Just a quick observation. While I appreciate the usefulness of this formula (I think!)

=IF(ISNUMBER(FIND("nex",C27)),"ok","no")

it does throw up a false positive if Cell C27 = "annexe".

To my little brain, if I want to do something because the first 3 characters = "nex" then I'd stick with the first answer,

=IF(LEFT(C27,3) = "nex","Yes","No")

Best regards,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
D€$
 
Upvote 0
Not sure whether I should have started a new thread. I'm new to this board. Please forgive any rookie mistakes I have made.

Here is my dilemna:

In Excel 2010 (Windows XP) I have a spreadsheet (3 columns, 250 lines) with data from my 2012 online bank transactions. The column headings are as follows:

Column A = Date Column B = Payee Column C = Amount

I have 250 of lines of data. I want to write an "If/Then" statement to look into the data in Column B and find the payee so that I can build one column for each payee and total those columns to see what I've paid to each payee during the year. This would be simple if the payee cell contained the name of the payee only. Unfortunately, every payee cell starts with
a unique descriptor "Online Paymentxxxxx33 To" with a unique Payment number for each payment. I want excel to look through the cell data in Col B to look for the defined payee. For example:


Col A Col B Col C
Row 1 12/31/12 Online Payment3009954333 To MrExcel 12/31 $100.00
Row 2 12/22/12 Online Payment3009912121 To Apple Store 12/22 $200.00

My thought was to write a formul in Col D, Row 1 that says: =If(B1="*MrExcel*", +C1,0) where the * is a wildcard. When I do that,
I get a "The formula contains an error" message.
I tried using a variation on your suggested =IF(LEFT(A1,3)="NEX","OK","NO") which I thought would be an eligent solution.
I get the same "The formula contains an error" message.

Any suggestions?
 
Last edited:
Upvote 0
It may be easier to understand my dilemma if you consider this look at the original spreadsheet:

Column A<o:p></o:p>
Column B<o:p></o:p>
Column C<o:p></o:p>
Row 1<o:p></o:p>
1/11/2012<o:p></o:p>
Online Paymentxxx54333 To MrExcel 01/11<o:p></o:p>
$ 100.00 <o:p></o:p>
Row 2<o:p></o:p>
1/12/2012<o:p></o:p>
Online Paymentxxx11221 To Apple 01/12<o:p></o:p>
$ 50.00 <o:p></o:p>
<tbody> </tbody>
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top