If A3=alpha numeric,"X", if A3=text,"Y", Blank
On 7 Aug 2006 11:39:22 -0700, "Harlan Grove" 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
|