IF Statement with Wildcard?

[Dan Reynolds]

Greetings

Is it possible to use the "IF" worksheet function to look at a text field
and return a value if, for example, the text begins with xyz?

something like:

A1 contains the text: smith

A2 has this formula:

=if(A1 starts with "smi", A1,0)

or

=if(A1=smi*, A1,0)

thanks in advance
Dan

 

[Don Guillett]

Have a look in HELP for the LEFT function to do this

 

[Rafael Ortiz]

Try something like:

=IF(A1>"smi",A1,0)

MRO

 

[Dan Reynolds]

Have a look in HELP for the LEFT function to do this

Thanks Don

I will use Rafael's solution, but LEFT will come in handy for me also.

Dan

 

[Myrna Larson]

sna is "greater than" smi. So are soap, special, squash, stove, sub, swan, and sylvan. None of
those begin with smi.

 

[Dan Reynolds]

Before you get too excited, Rafael's solution doesn't work. You said you wanted to test whether
the text in A1 begins with the letters smi. Any word that would come after

the non-existent word

Myrna

Probably a bad example I used. I think the formula will work for me because
my actual data looks like this:

T6111111
T6121111
T6131111
005989
007896
008524
T6141111

etc, and I only want to use the entries that begin with a letter, not a
number.

Dan

 

[Don Guillett]

etc, and I only want to use the entries that begin with a letter, not a
number.
If that is the case, why not =if(isnumber(a1),1,2)

 

[Phobos]

Give it up Don!

)

P

then back to my original of =if(left(a1,3)="wha",1,2)

 

[Jeff Darling]

I used left and mid all the time.

Here is an example using left.

=IF(LEFT(A4,3)="smi","Yes","No")

The condition isn't case sensitve so It returns "Yes" any
of these..

Smile
smile
sMile
smIle

etcetera...

 

[Rafael Ortiz]

I just presented an idea, folks. If you use your imagination a little, you
can make a solution "work" that suits this user's needs.

For example:

=IF(AND(A1>"smi",A1<"smj"),A1,"")

I tried that with "troubled" and it works. It's possible that this type of
solution is a little easier for the user to understand and implement than
some of the more complicated formulas others propose to use. Just my humble
opinion, though!!

Again, it's possible the user might have to tailor the solution, and that's
expected.

MRO

Before you get too excited, Rafael's solution doesn't work. You said you wanted to test whether
the text in A1 begins with the letters smi. Any word that would come after the non-existent word
"smi" in the dictionary will satisfy Rafael's formula. Try it with "troubled" in A1.

 

[Myrna Larson]

I just presented an idea, folks. If you use your imagination a little, you
can make a solution "work" that suits this user's needs.

True, but he asked a very specific question -- how to identify items that begin with "smi". Your
formula doesn't do that. Most folks here usually try to supply solutions to the question as
posed.

 

[Myrna Larson]

Now that you say your problem is to simply distinguish items that begin with a letter rather
than a digit, a couple more solutions are

=IF(A1>="A",....

=IF(CODE(A1)>64,....

or, if those items that begin with a digit consist *only* of digits

=IF(ISNUMBER(A1*1),....