| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Validating a Number ID
Hello,
I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan |
|
#2
|
|||
|
|||
|
Hi!
Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE(MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . |
|
#3
|
|||
|
|||
|
Hello Biff,
The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message ... Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE(MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . |
|
#4
|
|||
|
|||
|
Hi!
...doesn't seem to work for option 1, which is a straight 10 digit numeric ID It should work. That's the easiest condition to test for! It works in my test file. Are you sure the 10 char string is in fact a numeric value and not TEXT? Biff -----Original Message----- Hello Biff, The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message ... Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE(MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(-- SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . . |
|
#5
|
|||
|
|||
|
Hello Biff,
I have been entering in 10 character numeric values and the result is "False". The other 2 conditions work fine. I think I might know what the issue is. To make sure that the 9th character is uppercase, I am using the following to convert it - =PROPER(A1) This is obviously changing it to Text. How do I get around this? Thanks Ruan "Biff" wrote in message ... Hi! ...doesn't seem to work for option 1, which is a straight 10 digit numeric ID It should work. That's the easiest condition to test for! It works in my test file. Are you sure the 10 char string is in fact a numeric value and not TEXT? Biff -----Original Message----- Hello Biff, The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message ... Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE(MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(-- SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . . |
|
#6
|
|||
|
|||
|
Hi!
Are you using this as a data validation rule via DataValidation, or, are you simply using this formula in a cell to return either TRUE or FALSE? The reason I ask is that as a DV rule, the formula is almost to it's length limit of 255 chars. A cell formula can be 1024 chars long. I don't understand why you're using PROPER. Can you provide more detail as to how this is being used? Biff -----Original Message----- Hello Biff, I have been entering in 10 character numeric values and the result is "False". The other 2 conditions work fine. I think I might know what the issue is. To make sure that the 9th character is uppercase, I am using the following to convert it - =PROPER(A1) This is obviously changing it to Text. How do I get around this? Thanks Ruan "Biff" wrote in message ... Hi! ...doesn't seem to work for option 1, which is a straight 10 digit numeric ID It should work. That's the easiest condition to test for! It works in my test file. Are you sure the 10 char string is in fact a numeric value and not TEXT? Biff -----Original Message----- Hello Biff, The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message .. . Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE (MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(-- SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . . . |
|
#7
|
|||
|
|||
|
I am not using data validation.
Column A has all the IDs that where entered by Users. We have about 3,500 IDs. Column B has the Proper formula and Column C has your formula u gave me. Sometimes Users don't use Upper Case, so I am using Proper just to convert to Upper Case. Ruan "Biff" wrote in message ... Hi! Are you using this as a data validation rule via DataValidation, or, are you simply using this formula in a cell to return either TRUE or FALSE? The reason I ask is that as a DV rule, the formula is almost to it's length limit of 255 chars. A cell formula can be 1024 chars long. I don't understand why you're using PROPER. Can you provide more detail as to how this is being used? Biff -----Original Message----- Hello Biff, I have been entering in 10 character numeric values and the result is "False". The other 2 conditions work fine. I think I might know what the issue is. To make sure that the 9th character is uppercase, I am using the following to convert it - =PROPER(A1) This is obviously changing it to Text. How do I get around this? Thanks Ruan "Biff" wrote in message ... Hi! ...doesn't seem to work for option 1, which is a straight 10 digit numeric ID It should work. That's the easiest condition to test for! It works in my test file. Are you sure the 10 char string is in fact a numeric value and not TEXT? Biff -----Original Message----- Hello Biff, The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message . .. Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE (MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(-- SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . . . |
|
#8
|
|||
|
|||
|
Hi!
OK, if column B contains this formula: =PROPER(A1) Then the validation formula references column B, so Change this portion of the formula: =OR(AND(ISNUMBER(B1),...... To: =OR(AND(ISNUMBER(--B1), That will take care of the PROPER issue. Biff -----Original Message----- I am not using data validation. Column A has all the IDs that where entered by Users. We have about 3,500 IDs. Column B has the Proper formula and Column C has your formula u gave me. Sometimes Users don't use Upper Case, so I am using Proper just to convert to Upper Case. Ruan "Biff" wrote in message ... Hi! Are you using this as a data validation rule via DataValidation, or, are you simply using this formula in a cell to return either TRUE or FALSE? The reason I ask is that as a DV rule, the formula is almost to it's length limit of 255 chars. A cell formula can be 1024 chars long. I don't understand why you're using PROPER. Can you provide more detail as to how this is being used? Biff -----Original Message----- Hello Biff, I have been entering in 10 character numeric values and the result is "False". The other 2 conditions work fine. I think I might know what the issue is. To make sure that the 9th character is uppercase, I am using the following to convert it - =PROPER(A1) This is obviously changing it to Text. How do I get around this? Thanks Ruan "Biff" wrote in message .. . Hi! ...doesn't seem to work for option 1, which is a straight 10 digit numeric ID It should work. That's the easiest condition to test for! It works in my test file. Are you sure the 10 char string is in fact a numeric value and not TEXT? Biff -----Original Message----- Hello Biff, The alpha character is upper case. The formula works great for the valid options 2 and 3, but doesn't seem to work for option 1, which is a straight 10 digit numeric ID. 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) Thanks so much for your help. Ruan "Biff" wrote in message .. . Hi! Does it matter if the alpha char is upper or lower case? I see in your examples they're upper case. This will work for upper case: =OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE (MID (A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(-- SUBSTITUTE (A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID (A1,9,1)) =65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE (A1,MID (A1,9,1),"")))) If you want to accept lower case, then just change the CODE values to =97 and <=122. If you want either upper or lower, drop back and punt! You could use the CODE range from =65 and <=122 but that would leave open the possibilty that CHARS 91 through 96 could be entered. Biff -----Original Message----- Hello, I am struggling to write a formula to validate an ID Number. Here are the valid options - 1) All numeric 10 character ID (example: 1234567890) 2) Alphanumeric 10 character ID, with the 9th character being a Alpha character (example: 12345678E0) 3) Alphanumeric 14 character ID, with the 9th character being a Alpha character (example: 12345678A01234) The following examples have been used, but are not acceptable - 1) 1234567*9 2) 1234567*901234 3) 12345678'9 4) 12345678,9 Any help will be greatly appreciated. Thanks Ruan . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
| Random Number Questions | Excel Worksheet Functions | |||
| How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
| Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
