How do I use a wildcard in an excel formula?

Archon007 · External Since: Mar 14, 2007 Posts: 1

I am try to have an if command search a row of data and then print 1 if it
matchs and a 0 if it doesn't. However the data is a long string of numbers
that can be identified after reading the first 3. So instead of having to
type it all out I'd like it to do something like.

=if(a1=910*,1,0)

the * meaning anything after 910 that matches, so if a1 is 910111564 or
910465465 it will return 1, but if it is 911(anything) it will return a 0

Thanks

Teethless mama · External Since: Sep 17, 2006 Posts: 1529

=--ISNUMBER(FIND(910,A1))

"Archon007" wrote:

> I am try to have an if command search a row of data and then print 1 if it
> matchs and a 0 if it doesn't. However the data is a long string of numbers
> that can be identified after reading the first 3. So instead of having to
> type it all out I'd like it to do something like.
>
> =if(a1=910*,1,0)
>
> the * meaning anything after 910 that matches, so if a1 is 910111564 or
> 910465465 it will return 1, but if it is 911(anything) it will return a 0
>
> Thanks

Bob Davison · External Since: Nov 17, 2006 Posts: 21

=IF(LEFT(A1,3)="910",1,0)

"Archon007" wrote in message

>I am try to have an if command search a row of data and then print 1 if it
> matchs and a 0 if it doesn't. However the data is a long string of
> numbers
> that can be identified after reading the first 3. So instead of having to
> type it all out I'd like it to do something like.
>
> =if(a1=910*,1,0)
>
> the * meaning anything after 910 that matches, so if a1 is 910111564 or
> 910465465 it will return 1, but if it is 911(anything) it will return a 0
>
> Thanks

T. Valko · External Since: Nov 24, 2006 Posts: 3649

>>if a1 is 910111564 or 910465465 it will return 1
>=--ISNUMBER(FIND(910,A1))

123456910

Biff

"Teethless mama" wrote in message

> =--ISNUMBER(FIND(910,A1))
>
>
> "Archon007" wrote:
>
>> I am try to have an if command search a row of data and then print 1 if
>> it
>> matchs and a 0 if it doesn't. However the data is a long string of
>> numbers
>> that can be identified after reading the first 3. So instead of having
>> to
>> type it all out I'd like it to do something like.
>>
>> =if(a1=910*,1,0)
>>
>> the * meaning anything after 910 that matches, so if a1 is 910111564 or
>> 910465465 it will return 1, but if it is 911(anything) it will return a 0
>>
>> Thanks

Bob Davison · External Since: Nov 17, 2006 Posts: 21

Here are better ways to do it:

=IF(LEFT(A1,3)=TEXT($E$1,"0"),1,0) ...where the value in cell E1 equals the
3-digit search value.

or better...
=IF(LEFT(A1,LEN($E$1))=TEXT($E$1,"0"),1,0) ...where the value in cell E1
equals the search value of variable length.

or even...
=IF(LEFT(A1,LEN(numString))=TEXT(numString,"0"),1,0) ...where the value in
the single-cell named range "numString" equals a search value of variable
length.

This avoids hard-coding the formula.

Bob

"Bob Davison" wrote in message

> =IF(LEFT(A1,3)="910",1,0)
>
> "Archon007" wrote in message
>
>>I am try to have an if command search a row of data and then print 1 if it
>> matchs and a 0 if it doesn't. However the data is a long string of
>> numbers
>> that can be identified after reading the first 3. So instead of having
>> to
>> type it all out I'd like it to do something like.
>>
>> =if(a1=910*,1,0)
>>
>> the * meaning anything after 910 that matches, so if a1 is 910111564 or
>> 910465465 it will return 1, but if it is 911(anything) it will return a 0
>>
>> Thanks
>
>