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

[Raja]

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?

 

[Stefi]

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:

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Jim Cone]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Steve Albert]

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

 

[Steve Albert]

Thanks,, that worked well!

- Steve

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" <[email protected]> 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