Format Problem

[cj21]

I have a list of products e.g

Product
123
1453
14547
23
234

I want a zero to go at the begiining of each product e.g

Product
0123
01453
014547
023
0234

I have done this through the format option then custom 0#######

However looking at the display in the top to the right of the fx sign
the numbers don't have there zeros is it possible to make them keep
there zero's.

Chris

 

[Guest]

Why don't you just format the column as a Text Format and enter in the zero
as part of the data entry. Formatting a number only changes the appearance
of the number, not its actual value.

 

[robert111]

you could convert your numbers to text then concatenate a text zero onto
each of them....

 

[cj21]

not really sure how to do this, could you send an example.

thanks for the help

chri

 

[cj21]

Problem is guys in reality my dataset has over 10000 entries, inputting
will take to long

chris

 

[gjcase]

Then Robert111's answer is your best bet; if your numbers are in colum
A, ann a column with ="0"&A1 & copy it down to get what you are after
You can then copy & Paste Special/values to put these back into colum
A. Keep in mind these will be text, not numbers. If you want numbers
then your current solution is your best bet, i.e., format the numbers t
look the way you want. After all, 0535=535, right?

---GJ

 

[cj21]

Thanks guys this has been reallly usefull. Could you check out my other
problem about the zeros. Whenever i apply a formula to my data it works
for the products that begin with a zero and then for the others it does
not work for some reason.

chris

 

[Ron Rosenfeld]

I have a list of products e.g

Product
123
1453
14547
23
234

I want a zero to go at the begiining of each product e.g

Product
0123
01453
014547
023
0234

I have done this through the format option then custom 0#######

However looking at the display in the top to the right of the fx sign
the numbers don't have there zeros is it possible to make them keep
there zero's.

No, not if you want to store them as "numbers". In the display next to the Fx
sign is the actual contents of the cell. Formatting does not apply here.

You would have to convert them to TEXT in order to have that location appear
the way you describe.
--ron