Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |