If A3=alpha numeric,"X", if A3=text,"Y", Blank

[Gary]

Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?

[VBA Noob]

This might work

=IF(ISNUMBER(A3),"X","")&IF(ISTEXT(A3),"Y","")

VBA Noob

[Bob Phillips]

Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" <[email protected]> wrote in message
news:uNoVf%[email protected]...
> Ok.....this is what am trying to do.
>
> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>
> any ideas?
>
>

[Gary]

Thanks Bob..but i didnt understand ur question.


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Keep it simple
>
> =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>
> what about if not a number and not Y?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:uNoVf%[email protected]...
>> Ok.....this is what am trying to do.
>>
>> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>>
>> any ideas?
>>
>>
>
>

[Gary]

ISNUMBER is not working because the value is alpha numeric. for example --
E456

Thanks
Gary

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Keep it simple
>
> =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>
> what about if not a number and not Y?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:uNoVf%[email protected]...
>> Ok.....this is what am trying to do.
>>
>> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>>
>> any ideas?
>>
>>
>
>

[Gary]

Thanks Bob..but i didnt understand ur question.


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Keep it simple
>
> =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>
> what about if not a number and not Y?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:uNoVf%[email protected]...
>> Ok.....this is what am trying to do.
>>
>> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>>
>> any ideas?
>>
>>
>
>

[Gary]

ISNUMBER is not working because the value is alpha numeric. for example --
E456

Thanks
Gary

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Keep it simple
>
> =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>
> what about if not a number and not Y?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:uNoVf%[email protected]...
>> Ok.....this is what am trying to do.
>>
>> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>>
>> any ideas?
>>
>>
>
>

[Bob Phillips]

That is exactly the point I was making, it is not a number, not Y, so what
do you want the formula to return in this case?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" <[email protected]> wrote in message
news:[email protected]...
> ISNUMBER is not working because the value is alpha numeric. for example --
> E456
>
> Thanks
> Gary
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Keep it simple
> >
> > =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
> >
> > what about if not a number and not Y?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Gary" <[email protected]> wrote in message
> > news:uNoVf%[email protected]...
> >> Ok.....this is what am trying to do.
> >>
> >> If A3=alpha numeric,"X", if A3=text,"Y", Blank
> >>
> >> any ideas?
> >>
> >>
> >
> >
>
>

[Gary]

all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> That is exactly the point I was making, it is not a number, not Y, so what
> do you want the formula to return in this case?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:[email protected]...
>> ISNUMBER is not working because the value is alpha numeric. for
>> example --
>> E456
>>
>> Thanks
>> Gary
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Keep it simple
>> >
>> > =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>> >
>> > what about if not a number and not Y?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Gary" <[email protected]> wrote in message
>> > news:uNoVf%[email protected]...
>> >> Ok.....this is what am trying to do.
>> >>
>> >> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>> >>
>> >> any ideas?
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

Then all you need is

=IF(ISNUMBER(A3),"X","Y")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" <[email protected]> wrote in message
news:[email protected]...
> all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
> should return X. if A3 has text then it should return Y
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > That is exactly the point I was making, it is not a number, not Y, so
what
> > do you want the formula to return in this case?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Gary" <[email protected]> wrote in message
> > news:[email protected]...
> >> ISNUMBER is not working because the value is alpha numeric. for
> >> example --
> >> E456
> >>
> >> Thanks
> >> Gary
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Keep it simple
> >> >
> >> > =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
> >> >
> >> > what about if not a number and not Y?
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (replace somewhere in email address with gmail if mailing direct)
> >> >
> >> > "Gary" <[email protected]> wrote in message
> >> > news:uNoVf%[email protected]...
> >> >> Ok.....this is what am trying to do.
> >> >>
> >> >> If A3=alpha numeric,"X", if A3=text,"Y", Blank
> >> >>
> >> >> any ideas?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Scoops]

Gary wrote:
> all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
> should return X. if A3 has text then it should return Y
>

Hi Gary

As far as Excel is concerned, E344 is text.

If your alpha numerics are always the same format (i.e the second
character is a number), you can try:

=IF(ISNUMBER(MID(A3,2,1)*1),"X","Y")

Regards

Steve

[Gary]

its not working bob....i want X if the value in A3 is like E344 or F678 etc.
ISNUMBER is not taking it as a number.

Thanks for the help.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Then all you need is
>
> =IF(ISNUMBER(A3),"X","Y")
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Gary" <[email protected]> wrote in message
> news:[email protected]...
>> all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
>> should return X. if A3 has text then it should return Y
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > That is exactly the point I was making, it is not a number, not Y, so
> what
>> > do you want the formula to return in this case?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Gary" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> ISNUMBER is not working because the value is alpha numeric. for
>> >> example --
>> >> E456
>> >>
>> >> Thanks
>> >> Gary
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Keep it simple
>> >> >
>> >> > =IF(ISNUMBER(A3),"X",IF(A3="Y",""))
>> >> >
>> >> > what about if not a number and not Y?
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (replace somewhere in email address with gmail if mailing direct)
>> >> >
>> >> > "Gary" <[email protected]> wrote in message
>> >> > news:uNoVf%[email protected]...
>> >> >> Ok.....this is what am trying to do.
>> >> >>
>> >> >> If A3=alpha numeric,"X", if A3=text,"Y", Blank
>> >> >>
>> >> >> any ideas?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Gary]

wow steve..i tried MID but dint do *1.

it worked...thanks a ton

"Scoops" <[email protected]> wrote in message
news:[email protected]...
>
> Gary wrote:
>> all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
>> should return X. if A3 has text then it should return Y
>>
>
> Hi Gary
>
> As far as Excel is concerned, E344 is text.
>
> If your alpha numerics are always the same format (i.e the second
> character is a number), you can try:
>
> =IF(ISNUMBER(MID(A3,2,1)*1),"X","Y")
>
> Regards
>
> Steve
>

[Leo Heuser]

"Gary" <[email protected]> skrev i en meddelelse
news:[email protected]...
> all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
> should return X. if A3 has text then it should return Y
>


Hi Gary

Maybe this one:

=IF(ISNUMBER(A3)+(A3=""),"",IF(MIN(LEN(SUBSTITUTE(A3,{0,1,2,3,4,5,6,7,8,9},"")))<LEN(A3),"X","Y"))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

[Ron Rosenfeld]

On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" <[email protected]> wrote:

>Ok.....this is what am trying to do.
>
>If A3=alpha numeric,"X", if A3=text,"Y", Blank
>
>any ideas?
>

Your request is a little vague for me.

What is your definition of alpha numeric?

In other words, is there a particular sequence of letters and numbers, or is it
any sequence which contains both letters and numbers?

Also, do the letters have to be restricted to the set [A-Z] (capital letters
only) or are there other possibilities?

What is your definition of TEXT?

In Excel, text can include the set of numbers, depending on how they have been
entered. It can also include any non-alphabetic character. Your expectations
may differ.
--ron

[Gary]

Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.

Text = All text, no numbers


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" <[email protected]> wrote:
>
>>Ok.....this is what am trying to do.
>>
>>If A3=alpha numeric,"X", if A3=text,"Y", Blank
>>
>>any ideas?
>>
>
> Your request is a little vague for me.
>
> What is your definition of alpha numeric?
>
> In other words, is there a particular sequence of letters and numbers, or
> is it
> any sequence which contains both letters and numbers?
>
> Also, do the letters have to be restricted to the set [A-Z] (capital
> letters
> only) or are there other possibilities?
>
> What is your definition of TEXT?
>
> In Excel, text can include the set of numbers, depending on how they have
> been
> entered. It can also include any non-alphabetic character. Your
> expectations
> may differ.
> --ron

[Ron Rosenfeld]

On Mon, 7 Aug 2006 05:50:24 -0600, "Gary" <[email protected]> wrote:

>Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.
>
>Text = All text, no numbers
>
>

Assuming that when you write Text you mean only large or small letters
[A-Za-z], then, although it could be done using worksheet formulas, it's easier
using regular expressions.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr (This can be easily distributed with workbooks if that
is an issue).

Then try this formula:

=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))

The first regular expression restricts the length of the entry to four
characters. If there may be leading or trailing spaces to be ignored, the
formula will need to be modified.


--ron

[Harlan Grove]

Ron Rosenfeld wrote...
....
>Assuming that when you write Text you mean only large or small letters
>[A-Za-z], then, although it could be done using worksheet formulas, it's easier
>using regular expressions.
....

Text could just mean no numerals, \D.

>Then try this formula:
>
>=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))
....

You're restricting the alphanumeric test to upper case letters only.
Myself, I'd use

=IF(REGEX.COMP(A1,"^[A-Z]\d{3}$",0),"X",IF(REGEX.COMP(A1,"^\D+$"),"Y",""))

But this could be done without MOREFUNC.

=IF(AND(ABS(CODE(UPPER(A1)&" ")-77.5)<13,COUNT(-MID(A1,2,3))),"X",
IF(AND(A1<>"",COUNT(-MID(A1,{1,2,3,4},1))=0),"Y",""))

[Ron Rosenfeld]

On 7 Aug 2006 11:39:22 -0700, "Harlan Grove" <[email protected]> wrote:

>Ron Rosenfeld wrote...
>...
>>Assuming that when you write Text you mean only large or small letters
>>[A-Za-z], then, although it could be done using worksheet formulas, it's easier
>>using regular expressions.
>...
>
>Text could just mean no numerals, \D.
>
>>Then try this formula:
>>
>>=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))
>...
>
>You're restricting the alphanumeric test to upper case letters only.
>Myself, I'd use
>
>=IF(REGEX.COMP(A1,"^[A-Z]\d{3}$",0),"X",IF(REGEX.COMP(A1,"^\D+$"),"Y",""))
>
>But this could be done without MOREFUNC.
>
>=IF(AND(ABS(CODE(UPPER(A1)&" ")-77.5)<13,COUNT(-MID(A1,2,3))),"X",
>IF(AND(A1<>"",COUNT(-MID(A1,{1,2,3,4},1))=0),"Y",""))

Different ways to skin a cat :-). And had I reviewed the available commands
before posting, I would have used REGEX.COMP rather than REGEX.COUNT.

Based on the OP's response to me, though, I purposely restricted the initial
character to caps [A-Z], and the definition of text to what I posted in my
response [A-Za-z]. If his definition of text is "anything not a digit" rather
than everything in the set of capital and small letters, he should use the \D.

Thanks.


--ron