Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
How can I format a cell so that it will only allow seven digits to be entered. It must not allow less or more than seven. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=521479 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
grahammal wrote:
How can I format a cell so that it will only allow seven digits to be entered. It must not allow less or more than seven. Can the number have leading zeros? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
I think no such format is available to restrict a cell to accept only 7 alphabets, but validation can restrict it: Data-Validation Allow=Text length Data=Equal to length=7 click ok Now just type any seven alphabets in your active cell. Cheers...Jamex -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=521479 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
grahammal wrote:
How can I format a cell so that it will only allow seven digits to be entered. It must not allow less or more than seven. Assuming that leading zeros are allowed/expected: 1) Select the cell 2) Press Ctrl-1 3) On the Number tab select Custom 4) In the Type box type 0000000 5) Click OK 6) Go to Data-Validation 7) On the Settings tab select Allow Custom 8) Assuming that the cell in question is F8 in the Formula box type: =AND(ISNUMBER(F8),(LEN(TEXT(F8,"0000000"))=7)) 9) Click OK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
The number will never have leading zero's. As far as I can tell it will always begin with a 5. Examples 5393276 5393312 5393331 5393454 -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=521479 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
grahammal wrote:
The number will never have leading zero's. As far as I can tell it will always begin with a 5. Examples 5393276 5393312 5393331 5393454 In that case you can change the formula in step 8 of my other post to: =AND(ISNUMBER(F8),(LEN(F8)=7)) and leave out steps 2 to 5 inclusive. Enjoy!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
jamex wrote:
I think no such format is available to restrict a cell to accept only 7 alphabets, but validation can restrict it: Data-Validation Allow=Text length Data=Equal to length=7 click ok Now just type any seven alphabets in your active cell. Cheers...Jamex The requirement was for digits not alpha-numeric. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
Using the Allow: Text length
seems to not exclude numbers, although the use of the word text suggests so,, Jim "jamex" wrote in message ... I think no such format is available to restrict a cell to accept only 7 alphabets, but validation can restrict it: Data-Validation Allow=Text length Data=Equal to length=7 click ok Now just type any seven alphabets in your active cell. Cheers...Jamex -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=521479 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
Use Data Validation with a type of whole number and values of 1111111 and
9999999 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "grahammal" wrote in message ... How can I format a cell so that it will only allow seven digits to be entered. It must not allow less or more than seven. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=521479 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
One more...
Whole Number Between: Minimum: 5000000 Maximum: 5999999 grahammal wrote: The number will never have leading zero's. As far as I can tell it will always begin with a 5. Examples 5393276 5393312 5393331 5393454 -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=521479 -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
Dave Peterson wrote:
One more... Whole Number Between: Minimum: 5000000 Maximum: 5999999 But he isn't SURE that it always begins with 5. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
Bob Phillips wrote:
Use Data Validation with a type of whole number and values of 1111111 and 9999999 Surely it'd need to be 1000000 to 9999999 otherwise 1023456 for instance would not be valid. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
But he is sure as far as he can tell.
Which is probably the best any of us can say about anything. Paul Lautman wrote: Dave Peterson wrote: One more... Whole Number Between: Minimum: 5000000 Maximum: 5999999 But he isn't SURE that it always begins with 5. -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Formatting
Dave Peterson wrote:
But he is sure as far as he can tell. Which is probably the best any of us can say about anything. Too true :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting For A Cell Other Than The One With The Form | Excel Worksheet Functions | |||
Drop Down List that has formatting of cell range Font ect. | Excel Discussion (Misc queries) | |||
can conditional formatting on one cell value to another cell valu | Excel Worksheet Functions | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel |