This has to be an easy one, but I can't find it. How do I retain the leading
zero in a number and retain number formatting?

Sean

If these are the same length (say six characters) go to
FormatCells...Custom and enter 000000

Otherwise, your stuck with text formatting or a leading apostrophe (same
thing really)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Sean Timmons" wrote in message
...
This has to be an easy one, but I can't find it. How do I retain the
leading
zero in a number and retain number formatting?

To display 123 as 0123 use the custom format "0000" (no quotes, of course)
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sean Timmons" wrote in message
...
This has to be an easy one, but I can't find it. How do I retain the
leading
zero in a number and retain number formatting?

Say you have a number like 01234 - a total for five digits.

Format Cells... Number Custom and make it 00000

You will always get the leading zero, even if you only entered 4 digits.
--
Gary's Student


"Sean Timmons" wrote:

This has to be an easy one, but I can't find it. How do I retain the leading
zero in a number and retain number formatting?

On Wed, 27 Dec 2006 12:41:01 -0800, Sean Timmons
wrote:

This has to be an easy one, but I can't find it. How do I retain the leading
zero in a number and retain number formatting?

Two ways typically:
- The most common is to format the cell as Text. (Format- Cells -
Text)
- Another less common one is to use the custom format 0########, where
you put as many hash signs as the maximum expected width of the entry.

(I have no idea what that is in Excel 2007's "ribbons" or whatever the
hell they are, nor do I much want to at this point.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

You can also enter the 'number' as 'o123 (leading quote, and then right
justify it
This will still 'function' as a numeric value
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sean Timmons" wrote in message
...
This has to be an easy one, but I can't find it. How do I retain the
leading
zero in a number and retain number formatting?

On Wed, 27 Dec 2006 17:10:21 -0400, "Bernard Liengme"
wrote:

You can also enter the 'number' as 'o123 (leading quote, and then right
justify it
This will still 'function' as a numeric value

I think you mean '0123, rather than 'o123.

(The number '0' will still let the value be used in calculations when
the entry's in text format (including formatting it that way by using
a leading quote mark as above), but the letter 'o' doesn't.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

My Excel 2003 does not recognize a string such as '0123 as a numeric.

Could either of you two gentlemen explain how the string as above can funtion in
calculations "as is"?


Gord


On Thu, 28 Dec 2006 09:11:18 +1100, Hank Scorpio wrote:

On Wed, 27 Dec 2006 17:10:21 -0400, "Bernard Liengme"
wrote:

You can also enter the 'number' as 'o123 (leading quote, and then right
justify it
This will still 'function' as a numeric value

I think you mean '0123, rather than 'o123.

(The number '0' will still let the value be used in calculations when
the entry's in text format (including formatting it that way by using
a leading quote mark as above), but the letter 'o' doesn't.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

I think it depends on what you do with that cell:

With '0123 in A1:
=a1+5
returns 128

=sum(a:a)
won't include 123 in that sum.

Gord Dibben wrote:

My Excel 2003 does not recognize a string such as '0123 as a numeric.

Could either of you two gentlemen explain how the string as above can funtion in
calculations "as is"?

Gord

On Thu, 28 Dec 2006 09:11:18 +1100, Hank Scorpio wrote:

On Wed, 27 Dec 2006 17:10:21 -0400, "Bernard Liengme"
wrote:

You can also enter the 'number' as 'o123 (leading quote, and then right
justify it
This will still 'function' as a numeric value

I think you mean '0123, rather than 'o123.

(The number '0' will still let the value be used in calculations when
the entry's in text format (including formatting it that way by using
a leading quote mark as above), but the letter 'o' doesn't.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

--

Dave Peterson

On Wed, 27 Dec 2006 17:30:58 -0600, Dave Peterson
wrote:

I think it depends on what you do with that cell:

With '0123 in A1:
=a1+5
returns 128

=sum(a:a)
won't include 123 in that sum.

Yup, what Dave said. Though I confess that I didn't check it beyond
simple arithmetic opertations because "leading 0" typically means
telephone or credit card numbers or similar, which generally don't
have calculations performed on them. The fact that you can, in some
cases, do so is really only an aside.

Gord Dibben wrote:

My Excel 2003 does not recognize a string such as '0123 as a numeric.

Could either of you two gentlemen explain how the string as above can funtion in
calculations "as is"?

Gord

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Thanks Dave

I tested using 5 of the Functions from the AutoSum drop-down and '0123 was not a
valid numeric.

Did not realize a simple =cellref + 5 would add correctly.


Gord

On Wed, 27 Dec 2006 17:30:58 -0600, Dave Peterson
wrote:

I think it depends on what you do with that cell:

With '0123 in A1:
=a1+5
returns 128

=sum(a:a)
won't include 123 in that sum.

Gord Dibben wrote:

My Excel 2003 does not recognize a string such as '0123 as a numeric.

Could either of you two gentlemen explain how the string as above can funtion in
calculations "as is"?

Gord

On Thu, 28 Dec 2006 09:11:18 +1100, Hank Scorpio wrote:

On Wed, 27 Dec 2006 17:10:21 -0400, "Bernard Liengme"
wrote:

You can also enter the 'number' as 'o123 (leading quote, and then right
justify it
This will still 'function' as a numeric value

I think you mean '0123, rather than 'o123.

(The number '0' will still let the value be used in calculations when
the entry's in text format (including formatting it that way by using
a leading quote mark as above), but the letter 'o' doesn't.)

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Gord Dibben MS Excel MVP

Thanks Hank.

I posted a reply to Dave also.


Gord

On Thu, 28 Dec 2006 11:00:28 +1100, Hank Scorpio wrote:

On Wed, 27 Dec 2006 17:30:58 -0600, Dave Peterson
wrote:

I think it depends on what you do with that cell:

With '0123 in A1:
=a1+5
returns 128

=sum(a:a)
won't include 123 in that sum.

Yup, what Dave said. Though I confess that I didn't check it beyond
simple arithmetic opertations because "leading 0" typically means
telephone or credit card numbers or similar, which generally don't
have calculations performed on them. The fact that you can, in some
cases, do so is really only an aside.

Gord Dibben wrote:

My Excel 2003 does not recognize a string such as '0123 as a numeric.

Could either of you two gentlemen explain how the string as above can funtion in
calculations "as is"?

Gord

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Gord Dibben MS Excel MVP