|
If A3=alpha numeric,"X", if A3=text,"Y", Blank
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
This might work =IF(ISNUMBER(A3),"X","")&IF(ISTEXT(A3),"Y","") VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=568883 |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
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" wrote in message ... 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
Thanks Bob..but i didnt understand ur question.
"Bob Phillips" wrote in message ... 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" wrote in message ... 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
ISNUMBER is not working because the value is alpha numeric. for example --
E456 Thanks Gary "Bob Phillips" wrote in message ... 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" wrote in message ... 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
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" wrote in message ... ISNUMBER is not working because the value is alpha numeric. for example -- E456 Thanks Gary "Bob Phillips" wrote in message ... 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" wrote in message ... 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
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" wrote in message ... 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" wrote in message ... ISNUMBER is not working because the value is alpha numeric. for example -- E456 Thanks Gary "Bob Phillips" wrote in message ... 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" wrote in message ... 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
Then all you need is
=IF(ISNUMBER(A3),"X","Y") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gary" wrote in message ... 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" wrote in message ... 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" wrote in message ... ISNUMBER is not working because the value is alpha numeric. for example -- E456 Thanks Gary "Bob Phillips" wrote in message ... 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" wrote in message ... 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
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 |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
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" wrote in message ... Then all you need is =IF(ISNUMBER(A3),"X","Y") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gary" wrote in message ... 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" wrote in message ... 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" wrote in message ... ISNUMBER is not working because the value is alpha numeric. for example -- E456 Thanks Gary "Bob Phillips" wrote in message ... 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" wrote in message ... 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
wow steve..i tried MID but dint do *1.
it worked...thanks a ton "Scoops" wrote in message oups.com... 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 |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
"Gary" skrev i en meddelelse
... 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. |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.
Text = All text, no numbers "Ron Rosenfeld" wrote in message ... On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" 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 |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
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","")) |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" 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 |
If A3=alpha numeric,"X", if A3=text,"Y", Blank
On Mon, 7 Aug 2006 05:50:24 -0600, "Gary" 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 |
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 |
| All times are GMT +1. The time now is 12:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com