Excel drops leading zeros

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Sep 22, 2008

• #2

When typing it in the cell you could always start it with an apostrophe '. But if you format as text and then type in the number it shouldn't drop the zero? You could also set a custom formatting of 00000.

Hope that helps.

 

Upvote 0

S

shanealbrandt

New Member

Joined

Aug 12, 2008

Messages

25

• Sep 22, 2008

• #3

Well the problem is I've got 800 rows of data typed in already, and I didn't want to have to retype everything.

I'm going to have this same problem with the zip code field too. Sure, I can format tjhe column for Zip code, but when i save it as a CSV file, it drops all of the zeros again.

I need it as a CSV file to I can import it into ADP.

Ideas? Anyone??

-S

 

Upvote 0

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Sep 22, 2008

• #4

Set the cell to text and then use this formula and then copy and paste values:

=rept("0",5-len(a1))&a1

Hope that helps.

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Sep 22, 2008

• #5

or:

text(a1,"00000")

 

Upvote 0

S

shanealbrandt

New Member

Joined

Aug 12, 2008

Messages

25

• Sep 22, 2008

• #6

That worked! Perfect, thanks!

 

Upvote 0