Custom Cell format

[Little Penny]

Is it possible to create a custom cell format so that all entries are
force to look as follows?

JI-1234567

The (JI-) are constant followed by a combination of seven digits.

Can I do this in excel or is a macro needed?

 

[JW]

Right click on the cell and select Format Cells. Select Custom under
the Category list box of the Number tab. In the Type field, enter
"JI-"####### and leave the quotes intact. Now, any 7 digit number
entered in that cell will be preceded by JI-.

HTH
-Jeff-

 

[Little Penny]

Thanks

Is there a way to make sure they put in the seven digits. I'm mean can
I have excel reject if it not in this format

 

[JW]

You can use Data Validation. Go to Data-->Validation. In the
Settings tab, set the Allow field to Whole Number, the Data field to
Between, the Minimum to 1000000, and the Maximum to 9999999. This
will force them to enter a number between those two numbers. Now, if
they need to enter something that begins in 0, we will have to do a
bit more tweaking.

HTH
-Jeff-

 

[Guest]

Look at Data=>Validation

 

[Little Penny]

The first numerical digits does starts with a zero.

Example

JI-0457895

I guess this opens up a whole new can of worms...