Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How add a digit to front of number in each cell in excexl column | Excel Discussion (Misc queries) | |||
how do I add a zero in front of a bunch of 4 digit numbers? | Excel Discussion (Misc queries) | |||
How do I add a zero in front of a text formatt number? | Excel Worksheet Functions | |||
formating the number from 12345.1 to 12345.001 | Excel Discussion (Misc queries) | |||
how can i get a cell to hold a zero in front of a digit ie. 01 or. | Excel Discussion (Misc queries) |