Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front
 

Hi, How can I parse my excell column that contains numbers
(I changed them to be text) I want to put zeros in front
so the number has 6 digit. the number may have 3 4 or 5
digit so I need to fill 0 in front so it is 001234
thank you

Simon

Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front
 

Dim i as long, cell as Range
i = 1
do while not isempty(cells(i,1))
set cell = cells(i,1)
cell.Value = "'" & Right("000000" & cell.value,6)
i = i + 1
Loop


Change the 1 to refer to the column you want to parse.
--
Regards,
Tom Ogilvy

"simon.guertin" wrote in message
...
Hi, How can I parse my excell column that contains numbers
(I changed them to be text) I want to put zeros in front
so the number has 6 digit. the number may have 3 4 or 5
digit so I need to fill 0 in front so it is 001234
thank you

Simon

Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front
 

Thank you very much for the quick reply, but how do I use
this code?

-----Original Message-----
Dim i as long, cell as Range
i = 1
do while not isempty(cells(i,1))
set cell = cells(i,1)
cell.Value = "'" & Right("000000" & cell.value,6)
i = i + 1
Loop


Change the 1 to refer to the column you want to parse.
--
Regards,
Tom Ogilvy

"simon.guertin" wrote in
message
...
Hi, How can I parse my excell column that contains
numbers
(I changed them to be text) I want to put zeros in front
so the number has 6 digit. the number may have 3 4 or 5
digit so I need to fill 0 in front so it is 001234
thank you

Simon


.

Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front
 

OK it's fine I found out how to make your code work. I am
new with macros and excell!

Simon
-----Original Message-----
Dim i as long, cell as Range
i = 1
do while not isempty(cells(i,1))
set cell = cells(i,1)
cell.Value = "'" & Right("000000" & cell.value,6)
i = i + 1
Loop


Change the 1 to refer to the column you want to parse.
--
Regards,
Tom Ogilvy

"simon.guertin" wrote in
message
...
Hi, How can I parse my excell column that contains
numbers
(I changed them to be text) I want to put zeros in front
so the number has 6 digit. the number may have 3 4 or 5
digit so I need to fill 0 in front so it is 001234
thank you

Simon


.

Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front
 

If you don't want to change them to text but just want leading zeros, you
can also use a custom format. Highlight the cells, right click and select
Format Cells, click the Number tab, select Custom from the list of options
and then in the Type box, type 000000.

- John Michl



"Dana DeLouis" wrote in message
...
Just another general idea if you want to change numbers to text. Here,
the
prefix character for text is included in the format.

Sub Test()
Dim cell As Range
For Each cell In Range("A1:A20").Cells
cell = Format(cell, "'000000")
Next
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"simon.guertin" wrote in message
...
Hi, How can I parse my excell column that contains numbers
(I changed them to be text) I want to put zeros in front
so the number has 6 digit. the number may have 3 4 or 5
digit so I need to fill 0 in front so it is 001234
thank you

Simon