How to set a cell requested to input in the format of A123 (i.e. one letter
plus three digits) by data validation .

I believe this custom formula will work:

=AND(CODE(UPPER(LEFT(F3,1)))=65,CODE(UPPER(LEFT(F 3,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" wrote:

How to set a cell requested to input in the format of A123 (i.e. one letter
plus three digits) by data validation .

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",ISN UMBER(--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]






On Wed, 13 Jan 2010 09:48:01 -0800, yclhk
wrote:

How to set a cell requested to input in the format of A123 (i.e. one letter
plus three digits) by data validation .

ISNUMBER(--RIGHT(F3,3))

That will allow entries like:

A.00
A1e1
A10.
A1.1

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
I believe this custom formula will work:

=AND(CODE(UPPER(LEFT(F3,1)))=65,CODE(UPPER(LEFT(F 3,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" wrote:

How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .

ISNUMBER(--RIGHT(A1,3))

That will allow entries like:

A.00
A1e1
A10.
A1.1

--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...

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",ISN UMBER(--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]






On Wed, 13 Jan 2010 09:48:01 -0800, yclhk
wrote:

How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .

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 InsertNameDefine
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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"yclhk" wrote in message
...
How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .

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,


"T. Valko" wrote:

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 InsertNameDefine
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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"yclhk" wrote in message
...
How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .


.

- 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 the

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))

--
Biff
Microsoft Excel MVP


"yclhk" wrote in message
...
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,


"T. Valko" wrote:

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 InsertNameDefine
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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"yclhk" wrote in message
...
How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .


.