Custom number format

[N.F. Jackson]

I need to format cells for a case number of 9 digits, with hyphens as
indicated:

##-#-#####-# (we need to key in 9 digits and have the hyphens inserted)

Where the first two may ormay not be zeros and where the third set of five
may have leading zeros.

E.g. 97-3-00123-4 or 00-8-12345-6 or 08-1-00001-6

 

[Bernard Liengme]

How about: 00"-"0"-"00000"-"0
best wishes

 

[Dave Peterson]

Use a custom format of:
00-0-00000-0

I need to format cells for a case number of 9 digits, with hyphens as
indicated:

##-#-#####-# (we need to key in 9 digits and have the hyphens inserted)

Where the first two may ormay not be zeros and where the third set of five
may have leading zeros.

E.g. 97-3-00123-4 or 00-8-12345-6 or 08-1-00001-6

 

[Gord Dibben]

0#-#-#####-#

00-0-00000-0

Either one works for me


Gord Dibben MS Excel MVP

On Sat, 19 Jul 2008 08:25:00 -0700, N.F. Jackson <N.F.

 

[N.F. Jackson]

Thanks, Fellas... but none of them work all the time.

Using all the suggested formats I could not get the following input to yield
the desired format in parentheses following:

007123456 = (00-7-12345-6)
977123456 = (97-7-12345-6)

I think I actually had this down at one time, but can't reconstruct it, nor
find the workbook where I may have successfully created the format.

 

[Dave Peterson]

I'd try them again. I think you made a mistake.

Thanks, Fellas... but none of them work all the time.

Using all the suggested formats I could not get the following input to yield
the desired format in parentheses following:

007123456 = (00-7-12345-6)
977123456 = (97-7-12345-6)

I think I actually had this down at one time, but can't reconstruct it, nor
find the workbook where I may have successfully created the format.

 

[N.F. Jackson]

I tried it again. Still no cigar: Format 00-0-00000-0

Input

97012345 gets 00-9-77012-3 and 007123456 = 00-0-07123-5

 

[Dave Peterson]

It works fine for me.

I think something else is sticking its head in (maybe an event macro???)

If you start a new workbook and format A1 nicely, the type that value, do you
still have the same trouble?

 

[N.F. Jackson]

Thanks, Dave. In a new workbook it still inserts two zeros in the leading
two positions and truncates the end when I type in 977123456 and 007123456

 

[N.F. Jackson]

OK, think I have it... I turned off the fixed decimal = 2 in the
Tools/Options, then the custom number format worked for new case numbers
input into pre-formatted cells.

 

[Gord Dibben]

Thanks for posting back with the resolution.


Gord

OK, think I have it... I turned off the fixed decimal = 2 in the
Tools/Options, then the custom number format worked for new case numbers
input into pre-formatted cells.

 

[Dave Peterson]

I never use that setting and forget that others do.

You may have noticed that the value in the formula bar had two decimal places
after you entered the data. That would have been a bigger hint <bg>.

OK, think I have it... I turned off the fixed decimal = 2 in the
Tools/Options, then the custom number format worked for new case numbers
input into pre-formatted cells.

 

[N.F. Jackson]

Yes, the decimal should have been the hint! I often forget that there are two
places to change the fixed decimal, in the Options/Edit, and in the cell
properties. I do a lot of accounting analysis and often need the fixed
decimal.

THIS TIME I have recorded "Case Number Formatting Instructions" for myself
and all my staff.

Thanks for your help and patience. I knew that when the suggested format
worked for you but not for me, it had to be some other overriding setting of
mine that was boogering up the format, and find that Excel, awesome as it is,
often has hidden issues.