Format Cell and Data Validation

[Maki]

I'm creating a receipt form that's simple enough for non-Excel expert to use.

There's a column where you enter stuff called BSB number (Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and have
it displayed as 062-913. I'd also like to restrict the entry to six digits
exactly.

With Excel 2007, in Format Cells>Number, I chose Custom and specified Type:
###-###. Also with Data Validation, I put Allow: Text length, Data: equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example, error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?

 

[Rick Rothstein]

Try using 000-000 instead of ###-### for you cell format.

 

[Maki]

Thank you, Rick.

It does show the first zero now but I still get error message with data
validation.
How can I rectify this?

 

[Rick Rothstein]

What happens if you change the validation from "equal to 6" to "equal to or
less than 6" (or whatever XL2007's equivalent of that is)?

 

[Maki]

Yes, this did work!

Thank you, Rick!