default format for text in cells

[Sdlentertd]

I have three cells that people will be populating but i need to make
them with a default format and length.
so
Field 1 defalult will be character field 7 long 0123456
Field 2 defalult will be character field 9 long 012345678
Field 3 will be Numeric 06/01/2010 format

so if for the first field a person enters " 123456 (without the
leading 0) then an error message should pop up "enter 7 digits" ,
leaving the Field 1 blank until the 7 digits get entered..... same
with field 2
and Field 3 should have mm/dd/yyyy format

 

[Gord Dibben]

Do you want the digits to be text?

Do they have be preceded with a zero?

If user enters 1234567...........is that OK?

If user enters 12345.........do you want two zeros?

Would formatting field 1 as custom 0000000 be OK?

Explore the rules you can set up under Data Validation.


Gord Dibben MS Excel MVP

 

[Sdlentertd]

Do you want the digits to be text?

Do they have be preceded with a zero?

If user enters 1234567...........is that OK?

If user enters 12345.........do you want two zeros?

Would formatting field 1 as custom 0000000  be OK?

Explore the rules you can set up under Data Validation.

Gord Dibben     MS Excel MVP




- Show quoted text -

Do you want the digits to be text? - YES

 

[Gord Dibben]

For digits to considered as text you must precede them with an apostrophe.

'0123456 the apostrophe won't show in the cell.

Did you look at Data Validation>Allow?

You can set a text length validation with a message.

You can set validation for Date only with a message if not a valid date.

Since you want the digits to be text, custom formatting won't do you any good.

e.g. custom formatt to 0000000

Enter 123457 and Excel will change display to 012345

But that is display only..........the digits are considered as numbers by Excel.


Gord