Results 1 to 9 of 9
  1. #1
    kklott54
    Guest

    Excel 2000 Custom Format

    I need a format or formula that will automatically convert the contents of a cell to thousands. For instance, if I enter 55, the cell contents will reflect 55000, or 28.9, the cell contents will reflect 28900. Any help you can provide would be most appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Custom Format

    There is no way that I know of to do what you are asking using a format. It can easily be done using a formula like =A1*1000. However, not in the same cell as the value is entered into. The formula would have to be in another cell.

    To do it in the same cell that the value is entered into will require some VBA code in the Worksheet Change event. The following will multiply any numeric value entered into column C by 1000 for the worksheet the code is entered into.

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
    If IsNumeric(Target) Then
    Application.EnableEvents = False
    Target = Target * 1000
    Application.EnableEvents = True
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Custom Format

    If it's for display purposes only, that is if a 55 LOOKS like 55000 but is ACTUALLY still 55, then you can format it like this:
    0"000"

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Custom Format

    Bob: I don't think that will display 28.9 as 28900 will it?
    Legare Coleman

  5. #5
    kklott54
    Guest

    Re: Excel 2000 Custom Format

    Thanks, Bob, this is exactly what I needed!

  6. #6
    kklott54
    Guest

    Re: Excel 2000 Custom Format

    Nope, it won't do the decimals. I just checked. But for whole numbers it works. Any suggestions for the decimals, i.e. 28.7, or such.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Custom Format

    This is why I didn't suggest it in my first reply to your original message. My only suggestion is in my first reply.
    Legare Coleman

  8. #8
    kklott54
    Guest

    Re: Excel 2000 Custom Format

    Thank you!

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Custom Format

    Duh, no! It works for integers. Should have thought of that! 28.9 formatted as I suggest gives 28.9000!

Posting Permissions

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