HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?

M

Mike B

I need to fill cells 15 charecters in length with zeroes, the cells have
numbers in them already, ex. 45678 needs to read 000000000045678. The numers
in the cells are not the same length, some are 4 charecters up to 7
charechters. Can this be done?
 
M

Mustafa

I hope this may help;
Just open the cell's format, choose the "Number" tab, under the category
choose "Custom", then in the "Type" box write the 15 zeros.
"Type:"
000000000000000
this will do it i hope
 
J

JR

This is fine, but if I try to use a function after this like concantonate the
zero's go away. Essentialy I wan't to do the same thing as Mike B. I have a
list of numbers that are different lengths and I need them all to be front
filled w/ zeros up to nine digits. I can do this by using a custom format
for visual purposes, but I need to be able to concanonate another column into
that column and this doesn't seem to work for that.

ex. column1 coulmn2
10000 12345

use custom format gets me the following:

column1 coulmn2
10000 000012345

That is good but now I need to push column1 and column 2 together w/o
changing them. so I concantonated column1 and coulm 2 expecting to get:
10000000012345

but instead I get:
1000012345

It drops the front filled zeros. This is driving me crazy.
 
G

Gord Dibben

The zeros you are losing are not really there.

They are a result of the custom format.

=A1 & "0000" & B1 should do the trick.


Gord Dibben MS Excel MVP
 
G

ggugvrunt

You need to use a formula such as =RIGHT("000000000"&A1,9) to produce a 9
digit string with preceding zeros.
 
G

gwaubrey

I have another issue which is exactly related... but a little different twist.

I have a 14 character long number field that has to be parsed. Similar to
what my colleague has experienced, I had to format the numbers to assure that
there were a correct number of characters per field.

Here is and example of the number:
03103104290000

When I parse this info I need to retain the exact number of characters in
the string as follows (each number is now in a separate column):
03 103 10429 00 00

By formatting the columns with a custom number format I get the desired
result... ON THE SCREEN.

Now I need to add dashes in between each space so the numbers desired result
would be:
03-103-10429-00-00

If you use the CONCANTONATE function, or &, to string them together using
the following formula:
=A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5

the result is:
3-103-10429-0-0

So, like my friend here, this is driving me nuts. It gets to be a real pain
when the 3rd column (5 digets) is preceeded by a "0"... then I only have 4
digits in the field.

Any ideas on how to fix this??
 
R

Rick Rothstein

Try using the TEXT function instead of concatenation...

=TEXT(A1,"00-000-00000-00-00")
 
Top