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.

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.

"Janna" wrote:

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.

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 EditPaste SpecialValues to fix those
values in the cells.
--
Allllen


"Janna" wrote:

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.

Hi Janna

Assuming data in column A
Format Column B, FormatCellsNumberCustom 00000000000 (11 zeros)
In B1
=A1*100
Copy down as far as required.
Copy the whole of column B, Paste SpecialValues to fix the data
Delete column A if required.

--
Regards

Roger Govier


"Janna" wrote in message
...
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.

Thanks guys..That works for me!

"Janna" wrote:

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.