Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to type 123 & have it print 1.23 without using Options..edit?
I want to format a range of cells so that it automatically places the decimal
point as if it were money...i.e. type 123 & have it print 1.23. I want to do this without using the Options....edit which does it for the whole spreadsheet, thus not allowing different types of number formats elsewhere in the spreadsheet. I don't have any negative numbers, and I feel the answer is in the custom number format, but I haven't been able to figure how it works. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to type 123 & have it print 1.23 without using Options..edit?
This Custom Format will do what you're asking:
0\.00 However, be careful, because this ONLY affects the display, and not the number that is stored. So, 123 would appear as 1.23, but would still be treated as One-Hundred-Twenty-Three. If you need to use these cells for calculations, then first divide them by 100. HTH, Elkar "Jim Wyns" wrote: I want to format a range of cells so that it automatically places the decimal point as if it were money...i.e. type 123 & have it print 1.23. I want to do this without using the Options....edit which does it for the whole spreadsheet, thus not allowing different types of number formats elsewhere in the spreadsheet. I don't have any negative numbers, and I feel the answer is in the custom number format, but I haven't been able to figure how it works. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to type 123 & have it print 1.23 without using Options..edit?
Event code for a range can do what you want.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Application.Intersect(Range("A1:A30"), Target) Is Nothing Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("A" & n).Value = Excel.Range("A" & n).Value / 100 End If End If enditall: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 10:14:02 -0700, Jim Wyns <Jim wrote: I want to format a range of cells so that it automatically places the decimal point as if it were money...i.e. type 123 & have it print 1.23. I want to do this without using the Options....edit which does it for the whole spreadsheet, thus not allowing different types of number formats elsewhere in the spreadsheet. I don't have any negative numbers, and I feel the answer is in the custom number format, but I haven't been able to figure how it works. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting print options globally ? | Excel Discussion (Misc queries) | |||
Footer does not print on bottom of document | Excel Discussion (Misc queries) | |||
set the print area print multiple tabs | Excel Discussion (Misc queries) | |||
I want to type a poem and print it | New Users to Excel | |||
Printing - Have to set print area 1 column further than necessary | Excel Discussion (Misc queries) |