Reformatting a column

J

Janna

In Excel I have a column that contains a number of values, for example:
5231
306
345
70

I want to remformat the column as follows:
00000523100
00000030600
00000034500
00000007000

(add two zeros to end and pad the front with enough zeros so the column
contains 11 characters total)

Anyone know of a macro or formatting technique to do so easily? Thanks.
 
J

JLatham

Assuming they start at A1, in B1 use:
=A1 * 100
that will add your trailing 2 zeros.
Then use Format Cells and Custom and just type in 11 zeros as the format:
00000000000
and they will show up as desired, and still be numbers!
you could then Copy and Paste Special with Values selected to get rid of the
formula and make them values. The key to keeping the leading zeros is always
going to be the Format applied to the cells they are in.
 
A

Allllen

Or, if you really want them to have 11 digits and not be numbers any more
(maybe they are product codes or something), how about this.

Col A Col B
5231 =REPT("0",9-LEN(A2))&A2&"00"
306 =REPT("0",9-LEN(A3))&A3&"00"
345 etc
70 etc

Then you could copy Column B and do Edit>Paste Special>Values to fix those
values in the cells.
 
R

Roger Govier

Hi Janna

Assuming data in column A
Format Column B, Format>Cells>Number>Custom> 00000000000 (11 zeros)
In B1
=A1*100
Copy down as far as required.
Copy the whole of column B, Paste Special>Values to fix the data
Delete column A if required.
 
Top