Results 1 to 3 of 3
  1. #1
    liner
    Guest

    Format number puzzle (2003)

    I have a worsheet where I have a lot of values. Some of the values are integers; others have decimals present.
    --For all numbers, I would like the thousands marker (US format with comma).
    --For the integers, I do not want a decimal point.
    --For the values with decimals present, I want a decimal point and all of the decimals to appear (maximum of three numbers)

    Searching the archives, it looks like I might have to do a custom format, with some sort of logic in the [ ] section. At any rate, can you help me? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format number puzzle (2003)

    I don't think you can accomplish this with a custom format. You'd need VBA code to format each cell according to its value.

    If you want to format numbers as you enter them, you can use the Worksheet_Change event in the worksheet module, but that has several advantages:
    - It disables Undo.
    - Dates, times, percentages and currency values would all be formatted as numbers.

    Here is a macro that you can apply to the selected range:

    Sub FormatNumbers()
    Dim oCell As Range
    For Each oCell In Selection.Cells
    If IsDate(oCell) Then
    ' nothing
    ElseIf IsNumeric(oCell) Then
    If InStr(oCell.NumberFormat, "%") = 0 And _
    InStr(oCell.NumberFormat, "$") = 0 Then
    If oCell = Fix(oCell) Then
    oCell.NumberFormat = "#,##0"
    Else
    oCell.NumberFormat = "#,##0.###"
    End If
    End If
    End If
    Next oCell
    End Sub

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Format number puzzle (2003)

    You can't do that with a single number format as far as I know, I'm afraid. You would end up with a decimal point after the integers as the closest thing.
    Regards,
    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •