Y
yclhk
How to set a cell requested to input in the format of A123 (i.e. one letter
plus three digits) by data > validation .
plus three digits) by data > validation .
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
Luke M
I believe this custom formula will work:
=AND(CODE(UPPER(LEFT(F3,1)))>=65,CODE(UPPER(LEFT(F3,1)))<=90,ISNUMBER(--RIGHT(F3,3)),LEN(F3)=4)
Checks if first character is a letter(character code greater than 65 [A] and
less than 90 [Z]), checks if last 3 characters create a number, and if total
length of text is 4.
=AND(CODE(UPPER(LEFT(F3,1)))>=65,CODE(UPPER(LEFT(F3,1)))<=90,ISNUMBER(--RIGHT(F3,3)),LEN(F3)=4)
Checks if first character is a letter(character code greater than 65 [A] and
less than 90 [Z]), checks if last 3 characters create a number, and if total
length of text is 4.
C
Chip Pearson
In the Validation dialog, choose Custom from the Allow list and use
the following formula:
=AND(LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
This will allow the first character to be either upper or lower case.
If you want to force upper case, use
=AND(EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
If you want to force lower case, use
=AND(EXACT(LEFT(A1,1),LOWER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
Of course, change all occurrences of A1 to your actual cell address.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
the following formula:
=AND(LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
This will allow the first character to be either upper or lower case.
If you want to force upper case, use
=AND(EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
If you want to force lower case, use
=AND(EXACT(LEFT(A1,1),LOWER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
Of course, change all occurrences of A1 to your actual cell address.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
T
T. Valko
ISNUMBER(--RIGHT(F3,3))
That will allow entries like:
A.00
A1e1
A10.
A1.1
--
Biff
Microsoft Excel MVP
That will allow entries like:
A.00
A1e1
A10.
A1.1
--
Biff
Microsoft Excel MVP
Luke M said:I believe this custom formula will work:
=AND(CODE(UPPER(LEFT(F3,1)))>=65,CODE(UPPER(LEFT(F3,1)))<=90,ISNUMBER(--RIGHT(F3,3)),LEN(F3)=4)
Checks if first character is a letter(character code greater than 65 [A]
and
less than 90 [Z]), checks if last 3 characters create a number, and if
total
length of text is 4.
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
yclhk said:How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data > validation .
T
T. Valko
ISNUMBER(--RIGHT(A1,3))
That will allow entries like:
A.00
A1e1
A10.
A1.1
--
Biff
Microsoft Excel MVP
That will allow entries like:
A.00
A1e1
A10.
A1.1
--
Biff
Microsoft Excel MVP
Chip Pearson said:In the Validation dialog, choose Custom from the Allow list and use
the following formula:
=AND(LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
This will allow the first character to be either upper or lower case.
If you want to force upper case, use
=AND(EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
If you want to force lower case, use
=AND(EXACT(LEFT(A1,1),LOWER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",ISNUMBER(--RIGHT(A1,3)))
Of course, change all occurrences of A1 to your actual cell address.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data > validation .
T
T. Valko
Hmmm...
My original reply hasn't shown up. I think I know why. Here it is again with
a slight modification (will add some delimiters to the defined name).
Try this...
Create this defined name
Goto Insert>Name>Define
Name: Letters
Refers to:
="ABCDEFGHIJKLMNOPQRSTUVW_X_Y_Z"
OK
Note: remove those underscores! (Let's see if this allows the reply to be
posted)
Let's assume you want to apply the validation to cell A1
Select cell A1
Goto Data>Validation
Allow: Custom
Formula:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
**Uncheck: Ignore blank**
OK
That will allow only the uppercase letters A-Z followed by 3 digits 0-9.
If you don't want to restrict the case of the letter, in the formula,
replace FIND with SEARCH.
My original reply hasn't shown up. I think I know why. Here it is again with
a slight modification (will add some delimiters to the defined name).
Try this...
Create this defined name
Goto Insert>Name>Define
Name: Letters
Refers to:
="ABCDEFGHIJKLMNOPQRSTUVW_X_Y_Z"
OK
Note: remove those underscores! (Let's see if this allows the reply to be
posted)
Let's assume you want to apply the validation to cell A1
Select cell A1
Goto Data>Validation
Allow: Custom
Formula:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
**Uncheck: Ignore blank**
OK
That will allow only the uppercase letters A-Z followed by 3 digits 0-9.
If you don't want to restrict the case of the letter, in the formula,
replace FIND with SEARCH.
Y
yclhk
Thank you for your formula, it works.
However I wish to know :
- what is meaning of "*" and "--" in the formula
- by using the formula of Luke M, why only A1e1 returns "True", and other
letter, like A1b1 will return "False"
Thanks again,
However I wish to know :
- what is meaning of "*" and "--" in the formula
- by using the formula of Luke M, why only A1e1 returns "True", and other
letter, like A1b1 will return "False"
Thanks again,
T
T. Valko
- what is meaning of "*" and "--" in the formula
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
The formula is a combination of 2 separate tests:
FIND(LEFT(A1),Letters)
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
The first test checks that the first character in the string is an uppercase
letter. If it is it returns that letter's position where it's found in the
named string Letters. For example, if the cell contained:
D100
Then the uppercase letter D is found at position 4 of the named string
Letters. So:
FIND(LEFT(A1),Letters) = 4
The second test checks to make sure the next 3 characters in the string D100
are the digits 0-9.
--MID(A1,2,3)
Starting from the 2nd character in the string D100, return the next 3
characters = "100". The MID function *always* returns a TEXT value even if
it looks like a number. There is a difference in the data types TEXT and
NUMBER. Excel treats those data types differently. So, we need to convert
the TEXT "100" to the numeric number 100. One way to do that is to use the
double unary minus --.
--"100" = 100
The first - converts the TEXT string "100" to a negative number:
-"100" = -100
The second - then converts the negative number back to a positive number:
--100 = 100
One possible problem with this is that Excel doesn't recognize leading 0s as
part of a numeric number. For example, if you try to enter the number 001 in
a cell Excel will automatically strip off the leading 0s so the cell entry
will be just the number 1.
This comes into play if your cell entry to be validated was D001:
MID(A1,2,3) = "001" but:
--MID(A1,2,3) will strip off those leading 0s leaving us with the single
number 1.
That's why we use the TEXT function:
TEXT(--MID(A1,2,3),"000")
The TEXT function returns the numeric number we extracted with the MID
function as a TEXT number in the format 000. This in effect will replace any
leading 0s that may have been stripped off by --MID(A1,2,3).
I'm pretty sure that by now you're getting confused!
Just hang in there!!!
So, if the cell entry was D001 then:
TEXT(--MID(A1,2,3),"000") = "001"
We now compare that result to MID(A1,2,4):
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
MID(A1,2,4):
Starting from the 2nd character in the string D001, return the next *4*
characters. Hmmm... there are only 3 characters! So why do you want to
return 4 characters?
If the string was D1234 that's too many characters since a valid entry must
have only 4 characters. That will cause this to fail the test:
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
"1234" = "123" = FALSE
In effect, this not only tests that the next 3 characters are the numbers
0-9 but it also acts as test to make sure the total length of the string is
4 characters.
MID(A1,2,4) will return up to the next 4 characters starting from the 2nd
character. If there aren't 4 characters it'll return whatever number of
characters are there:
D1234 = "1234"
D123 = "123"
D12 = "12"
D1 = "1"
D = ""
The only time the test will pass is when there there are *only* 3 numbers:
"1234" = "123" = FALSE
"123" = "123" = TRUE
"12" = "012" = FALSE
"1" = "001" = FALSE
"" = "000" = FALSE
So, the test for the 3 digits 0-9 will return either TRUE or FALSE. If the
cell entry was:
D123
Then:
FIND(LEFT(A1),Letters) = 4
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000")) = TRUE
We then multiply these resutls:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
4*TRUE = 4
Whenever the result of this multiplication is **any number other than 0**
then Excel evaluates that as being TRUE and passes the validation test
allowing the cell entry. If the cell entry was D1234 then:
4*FALSE = 0
When the first test fails:
FIND(LEFT(A1),Letters)
FIND will return a #VALUE! error and cause the entire formula:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
To return the #VALUE! error and therefore fail the test and not allow the
cell entry.
OK, your next question was:
1e1 is allowed because Excel evaluates 1e1 as a number in scientific
notation. 1e1 or 1E1 is scientific notation for the number 10. Try typing
1e1 into a cell and see what happens. Excel will convert that into the
number 1.00E+01. Change the cell format to General and it will now display
the number 10.
So, in Luke's formula:
1e1 passes the test:
ISNUMBER(--RIGHT(F3,3))
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
The formula is a combination of 2 separate tests:
FIND(LEFT(A1),Letters)
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
The first test checks that the first character in the string is an uppercase
letter. If it is it returns that letter's position where it's found in the
named string Letters. For example, if the cell contained:
D100
Then the uppercase letter D is found at position 4 of the named string
Letters. So:
FIND(LEFT(A1),Letters) = 4
The second test checks to make sure the next 3 characters in the string D100
are the digits 0-9.
--MID(A1,2,3)
Starting from the 2nd character in the string D100, return the next 3
characters = "100". The MID function *always* returns a TEXT value even if
it looks like a number. There is a difference in the data types TEXT and
NUMBER. Excel treats those data types differently. So, we need to convert
the TEXT "100" to the numeric number 100. One way to do that is to use the
double unary minus --.
--"100" = 100
The first - converts the TEXT string "100" to a negative number:
-"100" = -100
The second - then converts the negative number back to a positive number:
--100 = 100
One possible problem with this is that Excel doesn't recognize leading 0s as
part of a numeric number. For example, if you try to enter the number 001 in
a cell Excel will automatically strip off the leading 0s so the cell entry
will be just the number 1.
This comes into play if your cell entry to be validated was D001:
MID(A1,2,3) = "001" but:
--MID(A1,2,3) will strip off those leading 0s leaving us with the single
number 1.
That's why we use the TEXT function:
TEXT(--MID(A1,2,3),"000")
The TEXT function returns the numeric number we extracted with the MID
function as a TEXT number in the format 000. This in effect will replace any
leading 0s that may have been stripped off by --MID(A1,2,3).
I'm pretty sure that by now you're getting confused!
Just hang in there!!!
So, if the cell entry was D001 then:
TEXT(--MID(A1,2,3),"000") = "001"
We now compare that result to MID(A1,2,4):
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
MID(A1,2,4):
Starting from the 2nd character in the string D001, return the next *4*
characters. Hmmm... there are only 3 characters! So why do you want to
return 4 characters?
If the string was D1234 that's too many characters since a valid entry must
have only 4 characters. That will cause this to fail the test:
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
"1234" = "123" = FALSE
In effect, this not only tests that the next 3 characters are the numbers
0-9 but it also acts as test to make sure the total length of the string is
4 characters.
MID(A1,2,4) will return up to the next 4 characters starting from the 2nd
character. If there aren't 4 characters it'll return whatever number of
characters are there:
D1234 = "1234"
D123 = "123"
D12 = "12"
D1 = "1"
D = ""
The only time the test will pass is when there there are *only* 3 numbers:
"1234" = "123" = FALSE
"123" = "123" = TRUE
"12" = "012" = FALSE
"1" = "001" = FALSE
"" = "000" = FALSE
So, the test for the 3 digits 0-9 will return either TRUE or FALSE. If the
cell entry was:
D123
Then:
FIND(LEFT(A1),Letters) = 4
(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000")) = TRUE
We then multiply these resutls:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
4*TRUE = 4
Whenever the result of this multiplication is **any number other than 0**
then Excel evaluates that as being TRUE and passes the validation test
allowing the cell entry. If the cell entry was D1234 then:
4*FALSE = 0
When the first test fails:
FIND(LEFT(A1),Letters)
FIND will return a #VALUE! error and cause the entire formula:
=FIND(LEFT(A1),Letters)*(MID(A1,2,4)=TEXT(--MID(A1,2,3),"000"))
To return the #VALUE! error and therefore fail the test and not allow the
cell entry.
OK, your next question was:
- by using the formula of Luke M, why only
A1e1 returns "True", and other letter, like
A1b1 will return "False"
1e1 is allowed because Excel evaluates 1e1 as a number in scientific
notation. 1e1 or 1E1 is scientific notation for the number 10. Try typing
1e1 into a cell and see what happens. Excel will convert that into the
number 1.00E+01. Change the cell format to General and it will now display
the number 10.
So, in Luke's formula:
1e1 passes the test:
ISNUMBER(--RIGHT(F3,3))
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.