=Text(cell,"000000000") in VB
 

This worked great. I added the following to my macros

Columns("L:L").Select
Selection.NumberFormat = "000000000"

It formatted everything in the column that had a number value and left alone
my text header.

Thanks!

"sebastienm" wrote:

Hi Joe,

Say your range is M7:M50000
Range("M7:M50000").NumberFormat = "000000000"
so 123 shows as 000000123.
It is equivalent as setting the cell through the menu Format Cell, tab
Number...

Reagdrs,
Sebastien

"jgranda" wrote:

I am a novice VB writer. How do I format each cell in a column to show nine
digits including leading zeros? For example, 001234567 gets truncated to
1234567 and that is an undesired result. I am aware that I can write a
formula to refer to a cell to produce a nine digit text field.

For instance for column M to format column L, the formula I have seen is
=Text(L1,"000000000") where I use this formula in every cell in column M. I
am importing information into column L from a delimitted file. The
spreadsheet timed out when I imported the data (only 200 rows now but could
become 50,000 rows). It ran fine when I only computed 10 cells. Note the
first row to have values in column L is row 7.

There must be a better way to format so it always has nine digits and add
leading zeros rather than truncate.

Does anyone have any suggested VB code that I can add to format the data in
column L. It would be preferred not to add a new column but I can always
hide the original column for presentation purposes if it needs to be computed
in a new column.

Thank you.

Joe