Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
function or format - remove decimal but retain value
I need to take a column of numbers 1235.36 and change to 123536 with a number
of leading zeros. End result 0000123536. Is there a format or function to accomplish this task? Thanks for any suggestions. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
function or format - remove decimal but retain value
Select the cells you want to change and run this macro:
Sub fixer() For Each r In Selection With r v = "0000" & Replace(.Text, ".", "") .Clear .NumberFormat = "@" .Value = v End With Next End Sub It removes the decimal point and puts the 4 zeros in front. -- Gary''s Student - gsnu200776 "Geraldine" wrote: I need to take a column of numbers 1235.36 and change to 123536 with a number of leading zeros. End result 0000123536. Is there a format or function to accomplish this task? Thanks for any suggestions. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
function or format - remove decimal but retain value
On Fri, 28 Mar 2008 16:45:00 -0700, Geraldine
wrote: I need to take a column of numbers 1235.36 and change to 123536 with a number of leading zeros. End result 0000123536. Is there a format or function to accomplish this task? Thanks for any suggestions. =TEXT(INT(A1*100),"0000000000") If you require the values be numeric, then: =A1*100 and custom format the cell as "0000000000" --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
function or format - remove decimal but retain value
I'm thinking the number may vary in "length" and the number of leading
zeroes may vary with it. It looked to me like the OP might want to keep the field length fixed at 10 digits (using leading zeroes to pad it out to that length). Sub FixIt() Dim R As Range For Each R In Selection R.Value = Format(Replace(R.Value, ".", ""), "'0000000000") Next End Sub Instead of using NumberFormat, I simply preceded the cell value with a leading apostrophe. Is there any downside to enforcing the text format on the numerical result with the apostrophe rather than using a NumberFormat? By the way, my code may not be the answer the OP is looking for either... it depends on if the number of decimal places can vary and how that would affect the alignment of the number once the decimal point is removed. Rick "Gary''s Student" wrote in message ... Select the cells you want to change and run this macro: Sub fixer() For Each r In Selection With r v = "0000" & Replace(.Text, ".", "") .Clear .NumberFormat = "@" .Value = v End With Next End Sub It removes the decimal point and puts the 4 zeros in front. -- Gary''s Student - gsnu200776 "Geraldine" wrote: I need to take a column of numbers 1235.36 and change to 123536 with a number of leading zeros. End result 0000123536. Is there a format or function to accomplish this task? Thanks for any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retain decimal zeroes? | Excel Discussion (Misc queries) | |||
remove duplicates but retain one not delete both | Excel Discussion (Misc queries) | |||
Excel: remove cell content but retain formula | Excel Discussion (Misc queries) | |||
Is there a function or format to remove 'oz' from '12oz' in Excel? | Excel Worksheet Functions | |||
Increase / decrease decimal - retain cell formatting | Excel Programming |