Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i restrict data entry from certain columns | Excel Discussion (Misc queries) | |||
restrict data in a pivottable | Charts and Charting in Excel | |||
HOW TO RESTRICT DATA ENTRY FROM A CHOICE OF SEVERAL LIST? | Excel Discussion (Misc queries) | |||
Excel2000: Data Validation to restrict entries | Excel Discussion (Misc queries) | |||
How do I restrict data movement (cut & paste) to only one column? | Excel Discussion (Misc queries) |