|
Alphanumeric limitation
Looking for help to validate a single cell to an alphanumeric entry that is
exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
This works for alpha being upper case *only*:
=AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)),CODE(A1)=65,CODE(A1)<=90) To accept upper *or* lower case alpha: =AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)), OR(AND(CODE(A1)=65,CODE(A1)<=90),AND(CODE(A1)=97 ,CODE(A1)<=122))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
This works based on the following conditions:
Length must be 6 characters The 1st character must be upper case A-Z Chars 2 through 6 must be numbers 0-9 =AND(LEN(A1)=6,CODE(LEFT(A1))=65,CODE(LEFT(A1))<= 90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) -- Biff Microsoft Excel MVP "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
ISNUMBER(--RIGHT(A1,5))
That will accept the following: A1 = X1.234 A1 = X-1234 A1 = X123E1 -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... This works for alpha being upper case *only*: =AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)),CODE(A1)=65,CODE(A1)<=90) To accept upper *or* lower case alpha: =AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)), OR(AND(CODE(A1)=65,CODE(A1)<=90),AND(CODE(A1)=97 ,CODE(A1)<=122))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
Code() doesn't really need Left().
Besides, can't get your formula to accept: A12345 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... This works based on the following conditions: Length must be 6 characters The 1st character must be upper case A-Z Chars 2 through 6 must be numbers 0-9 =AND(LEN(A1)=6,CODE(LEFT(A1))=65,CODE(LEFT(A1))<= 90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) -- Biff Microsoft Excel MVP "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
You're right ... stupid of me!
Neat formula.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Code() doesn't really need Left(). Yeah, that'll save a few keystrokes. =AND(LEN(A1)=6,CODE(A1)=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) can't get your formula to accept: A12345 If you're testing it as a worksheet formula it has to be array entered. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Code() doesn't really need Left(). Besides, can't get your formula to accept: A12345 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... This works based on the following conditions: Length must be 6 characters The 1st character must be upper case A-Z Chars 2 through 6 must be numbers 0-9 =AND(LEN(A1)=6,CODE(LEFT(A1))=65,CODE(LEFT(A1))<= 90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) -- Biff Microsoft Excel MVP "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
On Fri, 25 Apr 2008 08:52:00 -0700, Dan wrote:
Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. =AND(LEN(A1)=6,CODE(A1)=65,CODE(A1)<=90,ISNUMBER(-MID(A1,{2,3,4,5,6},1))) if the first letter must be capitalized. If it can be upper or lower case, then: =AND(LEN(A1)=6,OR(AND(CODE(A1)=65,CODE(A1)<=90),A ND(CODE(A1)=97,CODE(A1)<=122)), ISNUMBER(-MID(A1,{2,3,4,5,6},1))) --ron |
Alphanumeric limitation
"Ron Rosenfeld" wrote in message
... On Fri, 25 Apr 2008 08:52:00 -0700, Dan wrote: Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. =AND(LEN(A1)=6,CODE(A1)=65,CODE(A1)<=90,ISNUMBER(-MID(A1,{2,3,4,5,6},1))) if the first letter must be capitalized. If it can be upper or lower case, then: =AND(LEN(A1)=6,OR(AND(CODE(A1)=65,CODE(A1)<=90),A ND(CODE(A1)=97,CODE(A1)<=122)), ISNUMBER(-MID(A1,{2,3,4,5,6},1))) --ron If the OP's intention is to use this as a DataValidation rule (which is my interpretation) you can't use formulas with array constants. -- Biff Microsoft Excel MVP |
Alphanumeric limitation
On Fri, 25 Apr 2008 21:58:51 -0400, "T. Valko" wrote:
If the OP's intention is to use this as a DataValidation rule (which is my interpretation) you can't use formulas with array constants. Good point. I did not consider that. --ron |
Alphanumeric limitation
Perfect! Thank you very much.
-- Dan "T. Valko" wrote: Code() doesn't really need Left(). Yeah, that'll save a few keystrokes. =AND(LEN(A1)=6,CODE(A1)=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) can't get your formula to accept: A12345 If you're testing it as a worksheet formula it has to be array entered. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Code() doesn't really need Left(). Besides, can't get your formula to accept: A12345 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... This works based on the following conditions: Length must be 6 characters The 1st character must be upper case A-Z Chars 2 through 6 must be numbers 0-9 =AND(LEN(A1)=6,CODE(LEFT(A1))=65,CODE(LEFT(A1))<= 90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) -- Biff Microsoft Excel MVP "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
Alphanumeric limitation
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Dan" wrote in message ... Perfect! Thank you very much. -- Dan "T. Valko" wrote: Code() doesn't really need Left(). Yeah, that'll save a few keystrokes. =AND(LEN(A1)=6,CODE(A1)=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) can't get your formula to accept: A12345 If you're testing it as a worksheet formula it has to be array entered. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Code() doesn't really need Left(). Besides, can't get your formula to accept: A12345 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... This works based on the following conditions: Length must be 6 characters The 1st character must be upper case A-Z Chars 2 through 6 must be numbers 0-9 =AND(LEN(A1)=6,CODE(LEFT(A1))=65,CODE(LEFT(A1))<= 90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5) -- Biff Microsoft Excel MVP "Dan" wrote in message ... Looking for help to validate a single cell to an alphanumeric entry that is exacly 6 characters in length with the first character required to be a letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect. -- Dan |
| All times are GMT +1. The time now is 11:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com