G
Gary
Ok.....this is what am trying to do.
If A3=alpha numeric,"X", if A3=text,"Y", Blank
any ideas?
If A3=alpha numeric,"X", if A3=text,"Y", Blank
any ideas?
V
VBA Noob
This might work
=IF(ISNUMBER(A3),"X","")&IF(ISTEXT(A3),"Y","")
VBA Noob
=IF(ISNUMBER(A3),"X","")&IF(ISTEXT(A3),"Y","")
VBA Noob
B
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)
=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)
G
Gary
Thanks Bob..but i didnt understand ur question.
Bob Phillips said:
G
Gary
ISNUMBER is not working because the value is alpha numeric. for example --
E456
Thanks
Gary
E456
Thanks
Gary
B
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)
do you want the formula to return in this case?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
G
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
should return X. if A3 has text then it should return Y
B
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)
=IF(ISNUMBER(A3),"X","Y")
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
S
Scoops
Gary said:
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
G
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.
ISNUMBER is not taking it as a number.
Thanks for the help.
G
Gary
wow steve..i tried MID but dint do *1.
it worked...thanks a ton
it worked...thanks a ton
L
Leo Heuser
Gary said:
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"))
G
Gary
Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.
Text = All text, no numbers
Text = All text, no numbers
Ron Rosenfeld said:
H
Harlan Grove
Ron Rosenfeld wrote...
....
Text could just mean no numerals, \D.
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",""))
....
....
Text could just mean no numerals, \D.
....
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",""))
R
Ron Rosenfeld
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
R
Ron Rosenfeld
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
R
Ron Rosenfeld
Different ways to skin a cat
. And had I reviewed the available commandsbefore 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