# Format a cell to keep leading zeros.

1. ## Format a cell to keep leading zeros.

I often deal with account numbers with leading zeros. When I place them in a
cell Excel automatically deletes the leading zeros. Is there a way to format
the cell to hold the zeros?

2. ## Re: Format a cell to keep leading zeros.

Use a custom number format of 000000 or as many zero as you need.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

message
news:E235622A-98EC-46FB-AFA1-89A8790895C0@microsoft.com...
>I often deal with account numbers with leading zeros. When I
>place them in a
> cell Excel automatically deletes the leading zeros. Is there a
> way to format
> the cell to hold the zeros?

3. ## Re: Format a cell to keep leading zeros.

Another approach is to use =FORMAT(A4,"0000000") which creates a string with
the correct number of leading zeros, rather than just displaying it in this
way.

Regards,
David Jessop

"Chip Pearson" wrote:

> Use a custom number format of 000000 or as many zero as you need.
>

>
> message
> news:E235622A-98EC-46FB-AFA1-89A8790895C0@microsoft.com...
> >I often deal with account numbers with leading zeros. When I
> >place them in a
> > cell Excel automatically deletes the leading zeros. Is there a
> > way to format
> > the cell to hold the zeros?

>
>
>

4. ## Re: Format a cell to keep leading zeros.

David

Excel has no FORMAT Function except in VBA.

I think you may have meant =TEXT(A4,"0000000")

This would leave the numbers as text and could not be readily used for
calculations.

Gord Dibben Excel MVP

On Wed, 5 Jan 2005 09:13:06 -0800, David Jessop <David
Jessop@discussions.microsoft.com> wrote:

>Another approach is to use =FORMAT(A4,"0000000") which creates a string with
>the correct number of leading zeros, rather than just displaying it in this
>way.
>
>Regards,
>David Jessop
>
>"Chip Pearson" wrote:
>
>> Use a custom number format of 000000 or as many zero as you need.
>>

>
>>
>> message
>> news:E235622A-98EC-46FB-AFA1-89A8790895C0@microsoft.com...
>> >I often deal with account numbers with leading zeros. When I
>> >place them in a
>> > cell Excel automatically deletes the leading zeros. Is there a
>> > way to format
>> > the cell to hold the zeros?

>>
>>
>>

5. ## Re: Format a cell to keep leading zeros.

Hey Chip

I'm doing an upload that requires a "number" format. However, alot of the
numbers have leading zeroes. Is "custom" 0000 considered a "number"
formatted field.

"Chip Pearson" wrote:

> Use a custom number format of 000000 or as many zero as you need.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> message
> news:E235622A-98EC-46FB-AFA1-89A8790895C0@microsoft.com...
> >I often deal with account numbers with leading zeros. When I
> >place them in a
> > cell Excel automatically deletes the leading zeros. Is there a
> > way to format
> > the cell to hold the zeros?

>
>
>

6. ## RE: Format a cell to keep leading zeros.

Put an Apostrophe (ie..'0001114) at the beginning. excel will then treat your account number as text

Odie

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1