padding with zeroes and spaces in excel

[spastica]

hello,

how can i pad a cell with zeroes? for example, i have records that must have a length of 10 characters, so if the number is 500.00, the left side must be padded with 5 zeros. is it possible to automate this for all the records (even if they have varying numbers?)

i would also like to do the same as above, but with the padding being spaces instead of zeros.

i am still not certain how to incorporate vbscript with excel...

 

[TheBitDoctor]

spastica:

You can't quite do what you want. If you leave the cell value as a numeric then you can not change it's internal representation. I mean you can't change 500.00 to 000500.00. What you can do is change how the cell value is displayed. By changing the cell format you can get 500.00 to be displayed as 00500.00 by setting the format type to custom and entering "00000.00" (without the quotes) as the format string. Numbers exceeding 5 digits will be displayed properly with however many digits as are needed.

This cell will still be treated as a numeric value so when sorted it will be placed in "numeric" order.

If you want to use spaces then you'll need to write a macro to append the proper number on the left. Something like:

Right(" " & ActiveCell.Value, 10)

When you do this however you will change the numeric value into a text value so things like sorting wont work as expected and many other properties will change as well.

Alternatively, you could do both. Use the format to get the cell to display the number as you want it and then use:

ActiveCell.Text

to output what the cells displays as a text string when you need it.

Hope this helps,
Ron