
Next: Syntax for using variables in a cell formula to r.. 
Author 
Message 
Archon007 External
Since: Mar 14, 2007 Posts: 1

Posted: 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

Posted: 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

Posted: 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

Posted: 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

Posted: 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
3digit 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 singlecell named range "numString" equals a search value of variable
length.
This avoids hardcoding 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 



