How do I restrict data as a Letter and three no. e.g. A233, B767
 

I want to ristrict the user to enter data in a format with One Letter and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in Excel?

How do I restrict data as a Letter and three no. e.g. A233, B767
 

Use Custom Data validation with this formula (adjust C2 to the real cell):
=AND(LEN(C2)=4,CODE(C2)=65,CODE(C2)<=90,CODE(MID( C2,2,1))=48,CODE(MID(C2,2,1))<=57,CODE(MID(C2,3,1 ))=48,CODE(MID(C2,3,1))<=57,CODE(MID(C2,4,1))=48 ,CODE(MID(C2,4,1))<=57)

Regards,
Stefi


€žRaja€� ezt Ã*rta:

I want to ristrict the user to enter data in a format with One Letter and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in Excel?

How do I restrict data as a Letter and three no. e.g. A233, B767
 

You might find Data Validation something you can use. Select all the cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in). Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had you
remember above. That will give you the basic validation; you can come back
later and fill in the information on the other tabs.... so OK your way back
to the spreadsheet. Now try an type in a number that does not match your
specification.

Rick


"Raja" wrote in message
...
I want to ristrict the user to enter data in a format with One Letter and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in
Excel?

How do I restrict data as a Letter and three no. e.g. A233, B7
 

Thaank you Rick.

It works prefectly.

I am not sure about the significance of changing the teh A1 to active cell.
For me I selected teh entire column and it works fine without me changing the
active cells.

Thank you again!!

I also can't figure out how to rate the posting!! so it would be helpful if
you could give me guidenace.
Take care.

"Rick Rothstein (MVP - VB)" wrote:

You might find Data Validation something you can use. Select all the cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in). Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had you
remember above. That will give you the basic validation; you can come back
later and fill in the information on the other tabs.... so OK your way back
to the spreadsheet. Now try an type in a number that does not match your
specification.

Rick


"Raja" wrote in message
...
I want to ristrict the user to enter data in a format with One Letter and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in
Excel?

How do I restrict data as a Letter and three no. e.g. A233, B767
 

Rick,

On xl2002...
NOT(ISNUMBER(LEFT(A1))) always returns True for me.
As LEFT(A1) returns a number wrapped in quote marks.
This seems to work... NOT(ISNUMBER(INT(LEFT(A1))))

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Rick Rothstein (MVP - VB)"
wrote in message
You might find Data Validation something you can use. Select all the cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in). Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had you
remember above. That will give you the basic validation; you can come back
later and fill in the information on the other tabs.... so OK your way back
to the spreadsheet. Now try an type in a number that does not match your
specification.
Rick

How do I restrict data as a Letter and three no. e.g. A233, B7
 

I hope you have come back to this thread... there is an error in my original
formula... it does not insure the first character is a letter (as opposed to
a punctuation mark, for example), only that the first character is not a
number. This formula should do what you originally asked for....

=AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91)

Rick



"Raja" wrote in message
...
Thaank you Rick.

It works prefectly.

I am not sure about the significance of changing the teh A1 to active
cell.
For me I selected teh entire column and it works fine without me changing
the
active cells.

Thank you again!!

I also can't figure out how to rate the posting!! so it would be helpful
if
you could give me guidenace.
Take care.

"Rick Rothstein (MVP - VB)" wrote:

You might find Data Validation something you can use. Select all the
cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in).
Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown
and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had
you
remember above. That will give you the basic validation; you can come
back
later and fill in the information on the other tabs.... so OK your way
back
to the spreadsheet. Now try an type in a number that does not match your
specification.

Rick


"Raja" wrote in message
...
I want to ristrict the user to enter data in a format with One Letter
and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in
Excel?

How do I restrict data as a Letter and three no. e.g. A233, B767
 

Thanks for posting your message... it got me to thinking and, actually, as
it turns out, my original code and your modification to it would never be
restrictive enough... insuring a value is not a number does not guarantee it
is a letter (as opposed to a punctuation mark, for example). I have just
posted this formula...

=AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91)

which I believe is restrictive enough, to the OP's message where he thanked
me... I sure hope he comes back to that sub-thread to read it.

Thanks again for your message.

Rick


"Jim Cone" wrote in message
...
Rick,

On xl2002...
NOT(ISNUMBER(LEFT(A1))) always returns True for me.
As LEFT(A1) returns a number wrapped in quote marks.
This seems to work... NOT(ISNUMBER(INT(LEFT(A1))))

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Rick Rothstein (MVP - VB)"
wrote in message
You might find Data Validation something you can use. Select all the cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in).
Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had you
remember above. That will give you the basic validation; you can come back
later and fill in the information on the other tabs.... so OK your way
back
to the spreadsheet. Now try an type in a number that does not match your
specification.
Rick

How do I restrict data as a Letter and three no. e.g. A233, B7
 

By the way, when I change my old formula for this new one, a warning message
saying...

"The Formula currently evaluates to an error. Do you want to continue"

appears (I'm not sure why though)... anyway, clicking the Yes button will
make the formula the governing criteria and entries into the cell(s) you
selected will be validated as you requested.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I hope you have come back to this thread... there is an error in my
original formula... it does not insure the first character is a letter (as
opposed to a punctuation mark, for example), only that the first character
is not a number. This formula should do what you originally asked for....

=AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91)

Rick



"Raja" wrote in message
...
Thaank you Rick.

It works prefectly.

I am not sure about the significance of changing the teh A1 to active
cell.
For me I selected teh entire column and it works fine without me changing
the
active cells.

Thank you again!!

I also can't figure out how to rate the posting!! so it would be helpful
if
you could give me guidenace.
Take care.

"Rick Rothstein (MVP - VB)" wrote:

You might find Data Validation something you can use. Select all the
cells
(or an entire column) that you want this restriction on and note which
is
the active cell (it's the one in the selection that is not shaded in).
Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown
and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had
you
remember above. That will give you the basic validation; you can come
back
later and fill in the information on the other tabs.... so OK your way
back
to the spreadsheet. Now try an type in a number that does not match your
specification.

Rick


"Raja" wrote in message
...
I want to ristrict the user to enter data in a format with One Letter
and
three number.

In Access I can use Input Mask. Is there a simliar kind of feature in
Excel?

How do I restrict data as a Letter and three no. e.g. A233, B7
 

In Excel 2007, how would I use this to restrict the cell to 2 numbers, 1
letter, then 3 numbers. For example, 14Q215

Thanks.

- Steve

"Rick Rothstein (MVP - VB)" wrote:

Thanks for posting your message... it got me to thinking and, actually, as
it turns out, my original code and your modification to it would never be
restrictive enough... insuring a value is not a number does not guarantee it
is a letter (as opposed to a punctuation mark, for example). I have just
posted this formula...

=AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91)

which I believe is restrictive enough, to the OP's message where he thanked
me... I sure hope he comes back to that sub-thread to read it.

Thanks again for your message.

Rick


"Jim Cone" wrote in message
...
Rick,

On xl2002...
NOT(ISNUMBER(LEFT(A1))) always returns True for me.
As LEFT(A1) returns a number wrapped in quote marks.
This seems to work... NOT(ISNUMBER(INT(LEFT(A1))))

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Rick Rothstein (MVP - VB)"
wrote in message
You might find Data Validation something you can use. Select all the cells
(or an entire column) that you want this restriction on and note which is
the active cell (it's the one in the selection that is not shaded in).
Click
Data/Validation from Excel's menu; select Custom in the Allow dropdown and
put this formula in in the Formula field...

=AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1))))

Note - change my A1 references to the address of the active cell I had you
remember above. That will give you the basic validation; you can come back
later and fill in the information on the other tabs.... so OK your way
back
to the spreadsheet. Now try an type in a number that does not match your
specification.
Rick

How do I restrict data as a Letter and three no. e.g. A233, B7
 

Change Ricks' DV formula to
=AND(LEN(K1)=6,ISNUMBER(--LEFT(K1,2)),ISNUMBER(--RIGHT(K1,3)),CODE(MID(K1,3,1))64,CODE(MID(K1,3,1) )<91)--David BiddulphSteve Albert wrote: In Excel 2007, how would I use this to restrict the cell to 2 numbers, 1 letter, then 3 numbers. For example, 14Q215 Thanks. - Steve "Rick Rothstein (MVP - VB)" wrote: Thanks for posting your message... it got me to thinking and, actually, as it turns out, my original code and your modification to it would never be restrictive enough... insuring a value is not a number does not guarantee it is a letter (as opposed to a punctuation mark, for example). I have just posted this formula... =AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91) which I believe is restrictive enough, to the OP's message where he thanked me... I sure hope he comes back to that sub-thread to read it. Thanks again for your message. Rick "Jim Cone" wrote in message ... Rick, On xl2002... NOT(ISNUMBER(LEFT(A1))) always returns True for me. As LEFT(A1) returns a number wrapped in quote marks. This seems to work... NOT(ISNUMBER(INT(LEFT(A1)))) Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Rick Rothstein (MVP - VB)" wrote in message You might find Data Validation something you can use. Select all the cells (or an entire column) that you want this restriction on and note which is the active cell (it's the one in the selection that is not shaded in). Click Data/Validation from Excel's menu; select Custom in the Allow dropdown and put this formula in in the Formula field... =AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1)))) Note - change my A1 references to the address of the active cell I had you remember above. That will give you the basic validation; you can come back later and fill in the information on the other tabs.... so OK your way back to the spreadsheet. Now try an type in a number that does not match your specification. Rick

How do I restrict data as a Letter and three no. e.g. A233, B7
 

Thanks,, that worked well!

- Steve

"David Biddulph" wrote:

Change Ricks' DV formula to
=AND(LEN(K1)=6,ISNUMBER(--LEFT(K1,2)),ISNUMBER(--RIGHT(K1,3)),CODE(MID(K1,3,1))64,CODE(MID(K1,3,1) )<91)--David BiddulphSteve Albert wrote: In Excel 2007, how would I use this to restrict the cell to 2 numbers, 1 letter, then 3 numbers. For example, 14Q215 Thanks. - Steve "Rick Rothstein (MVP - VB)" wrote: Thanks for posting your message... it got me to thinking and, actually, as it turns out, my original code and your modification to it would never be restrictive enough... insuring a value is not a number does not guarantee it is a letter (as opposed to a punctuation mark, for example). I have just posted this formula... =AND(LEN(K1)=4,ISNUMBER(--MID(K1,2,3)),CODE(K1)64,CODE(K1)<91) which I believe is restrictive enough, to the OP's message where he thanked me... I sure hope he comes back to that sub-thread to read it. Thanks again for your message. Rick "Jim Cone" wrote in message
... Rick, On xl2002... NOT(ISNUMBER(LEFT(A1))) always returns True for me. As LEFT(A1) returns a number wrapped in quote marks. This seems to work... NOT(ISNUMBER(INT(LEFT(A1)))) Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Rick Rothstein (MVP - VB)" wrote in message You might find Data Validation something you can use. Select all the cells (or an entire column) that you want this restriction on and note which is the active cell (it's the one in the selection that is not shaded in). Click Data/Validation from Excel's menu; select Custom in the Allow dropdown and put this formula in in the Formula field... =AND(LEN(A1)=4,ISNUMBER(--MID(A1,2,3)),NOT(ISNUMBER(LEFT(A1)))) Note - change my A1 references to the address of the active cell I had you remember above. That will give you the basic
validation; you can come back later and fill in the information on the other tabs.... so OK your way back to the spreadsheet. Now try an type in a number that does not match your specification. Rick