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

How about: 00"-"0"-"00000"-"0
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"N.F. Jackson" <N.F. wrote in message
...
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

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



N.F. Jackson wrote:

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

--

Dave Peterson

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.
wrote:

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

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" wrote:

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

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote:

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

Thanks for posting back with the resolution.


Gord

On Sat, 19 Jul 2008 13:28:01 -0700, N.F. Jackson
wrote:

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" wrote:

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

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.

N.F. Jackson wrote:

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" wrote:

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

N.F. Jackson wrote:

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" wrote:

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 wrote:

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" wrote:

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

N.F. Jackson wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson