Help!

How do I use a wildcard in an excel formula?

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  Syntax for using variables in a cell formula to r..  
Author Message
Archon007
External


Since: Mar 14, 2007
Posts: 1



PostPosted: Wed Mar 14, 2007 8:40 pm    Post subject: How do I use a wildcard in an excel formula?
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

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
Back to top
Teethless mama
External


Since: Sep 17, 2006
Posts: 1529



PostPosted: Wed Mar 14, 2007 9:54 pm    Post subject: RE: How do I use a wildcard in an excel formula? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=--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
Back to top
Bob Davison
External


Since: Nov 17, 2006
Posts: 21



PostPosted: Thu Mar 15, 2007 12:24 am    Post subject: Re: How do I use a wildcard in an excel formula? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=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
Back to top
T. Valko
External


Since: Nov 24, 2006
Posts: 3649



PostPosted: Thu Mar 15, 2007 1:36 am    Post subject: Re: How do I use a wildcard in an excel formula? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

>>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
Back to top
Bob Davison
External


Since: Nov 17, 2006
Posts: 21



PostPosted: Fri Mar 16, 2007 7:16 am    Post subject: Re: How do I use a wildcard in an excel formula? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum