Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Max_Rebo1
 
Posts: n/a
Default How do I make a number have leading zeros to fill width of cell?

I am trying to format existing data so that instead of being a set amount of
digits, say 2 for example, it fills out the column based on its width, say 6,
with leading zeros. Example - I have 55122 in a cell that has a width of 6,
and I want it to say 055122. I know that changing the cell format to text and
re-entering the data would fix this problem, but I want a way to change the
data without re-entering it.
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

You can use a custom format of 000000, but by width, do you mean that if the
cell is widened it gets extra leading digits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Max_Rebo1" wrote in message
...
I am trying to format existing data so that instead of being a set amount

of
digits, say 2 for example, it fills out the column based on its width, say

6,
with leading zeros. Example - I have 55122 in a cell that has a width of

6,
and I want it to say 055122. I know that changing the cell format to text

and
re-entering the data would fix this problem, but I want a way to change

the
data without re-entering it.



  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

If you know that you want 6 digits, you can use a custom format of:
000000
select the column
format|cells|number tab|custom category

You could try this custom format, too:
0*0

If you widen the cell, you may see what looks like a gap between the leading 0
and the number you typed into the cell. But if you widen/shrink that column,
you'll see that this gap disappears.




Max_Rebo1 wrote:

I am trying to format existing data so that instead of being a set amount of
digits, say 2 for example, it fills out the column based on its width, say 6,
with leading zeros. Example - I have 55122 in a cell that has a width of 6,
and I want it to say 055122. I know that changing the cell format to text and
re-entering the data would fix this problem, but I want a way to change the
data without re-entering it.


--

Dave Peterson
  #4   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

Hi,

Select the data region -- "Format" -- "Cells" -- Select "Custom" under
"Category" -- Enter 000000 under "Type" -- "OK"
(In fact you can see under "Sample", a preview of how the numbers will be
formatted, as you are enter the 0's under "Type")

Regards,
B. R. Ramachandran

"Max_Rebo1" wrote:

I am trying to format existing data so that instead of being a set amount of
digits, say 2 for example, it fills out the column based on its width, say 6,
with leading zeros. Example - I have 55122 in a cell that has a width of 6,
and I want it to say 055122. I know that changing the cell format to text and
re-entering the data would fix this problem, but I want a way to change the
data without re-entering it.

  #5   Report Post  
Max_Rebo1
 
Posts: n/a
Default

Thank you all, that worked great!!

"Max_Rebo1" wrote:

I am trying to format existing data so that instead of being a set amount of
digits, say 2 for example, it fills out the column based on its width, say 6,
with leading zeros. Example - I have 55122 in a cell that has a width of 6,
and I want it to say 055122. I know that changing the cell format to text and
re-entering the data would fix this problem, but I want a way to change the
data without re-entering it.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Leading Zeros with a twist. Steve D Excel Discussion (Misc queries) 1 July 30th 05 01:28 AM
How do I force leading zeros in an Excel cell? EricKei Excel Discussion (Misc queries) 2 June 15th 05 08:28 PM
Leading zeros Paul Excel Discussion (Misc queries) 4 June 12th 05 04:04 AM
Lookup returns #NA when search value (text) has leading zeros. M-Dickey Excel Worksheet Functions 1 May 10th 05 10:26 PM
Insert Leading Zeros hkslater Excel Worksheet Functions 7 November 17th 04 12:28 AM


All times are GMT +1. The time now is 11:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"