Copy numbers that are being custom formatted

**mame** · 11-13-2009, 04:37 PM

Column A has a custom format of 000. Therefore the numbers in that column always have leading zeroes if they're less than 3 digits long.

I want to copy these leading zeroes to another column, but I DON'T want to have to custom format the new column. I just want the values copied over with any leading zeroes, if applicable.

When I "Paste Special" and select "Values", numbers like "1" that show up as "001" in Column A will show up as "1" again in the new column. How can I get the actual value "001" to be all that's in the new column?

If it's easier for you, pretend I need to copy over these custom formatted values in to another program which does not retain any custom formatting.

Thanks!

**ron2k_1** · 11-13-2009, 04:48 PM

And using a formula wouldn't work? Cause if it doens't matter to you, then there are several ways to do that. One of them would be to just have a simple cell reference, i.e., =A1, etc.

Please advice further...

Ron

**mame** · 11-13-2009, 04:53 PM

Originally Posted by ron2k_1

And using a formula wouldn't work? Cause if it doens't matter to you, then there are several ways to do that. One of them would be to just have a simple cell reference, i.e., =A1, etc.

Please advice further...

Ron

Although "1" appears as "001", if you click on the cell it still has just the value of "1". I need it to actually be "001", not just appear that way.

**ChemistB** · 11-13-2009, 05:02 PM

If you want it to "be" 001 instead of 1, then it is no longer seen as a number. you would use the formula (e.g. in B1)

=TEXT(A1,"000")

If you want to remove the formula, you'd then Copy>Paste Special>Values over top of the formula.

**mame** · 11-13-2009, 05:06 PM

But if I compare B1 to 001, e.g., IF(B1=001), then it will fail, right? I want 001 to appear in B1. Excel is being silly.

**ChemistB** · 11-13-2009, 05:11 PM

But if I compare B1 to 001, e.g., IF(B1=001), then it will fail, right?

Correct! If in another cell, you wish to compare them, then you would need the formula

=IF(VALUE(B1)=A1,....)
or
=IF(B1+0=A1,....)

Why is it so important for the leading zeros to be real instead of virtual?

**xenixman** · 11-13-2009, 04:49 PM

How can I get the actual value "001" to be all that's in the new column?

The same way you got the numbers to retain the leading zeros in column A.
Why did you have to do it in column A?

Unless you want to make them TEXT.