Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number Display (Excel 97)

    Hello,
    I was wondering if it was possible in excel to display figures in '000s...meaning...if I type 1000000, can I have it display 1000 in the cell just by formatting it, and not use a calculation?
    Thanks,
    kingming

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

    Re: Number Display (Excel 97)

    Select the cells you want to format.
    Select Format | Cells...
    In the Number tab. select the Custom category.
    Enter <code>0,</code> in the Type box (zero followed by the thousands separator)
    Click OK.

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

    Re: Number Display (Excel 97)

    Try this Custom Format:

    #,###,
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    thanks hansV that works perfectly.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    I have a similar question. How can I make a number display as thousands, e.i., I'd like to enter the number 1 and have it display as $1,000.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Number Display (Excel 97)

    Format - cells - number(tab)
    Category: custom
    Type:$0",000"

    Steve

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

    Re: Number Display (Excel 97)

    Select the cell or cells.
    Select Format | Cells...
    Activate the Number tab (if necessary).
    Select the Custom category.
    Enter the following custom format in the Type box:

    <code>$ #,##0",000"</code>

    Click OK. the text between quotes will be added to every number you enter.

  8. #8
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    Thanks Hans.

  9. #9
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    Thanks to your great customizing cells instructions, the numbers I enter are now displaying as thousands (i.e., when I type 3, the cell displays $3,000).

    My new problem is that the cells customized with $#,##0",000"automatically round numbers up or down to the nearest thousand, rather than showing actual number entered (i.e., when I type 1.3 the number displays as $1,000, and when I enter 1.5, the number displays as $2,000). Is there a way to stop the rounding so the cell shows (and more importantly adds) $1,300 and $1,500?

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

    Re: Number Display (Excel 97)

    This is quite a different question - this can't be handled by a custom format. You could create a Worksheet_Change event procedure, but it has a disadvantage, as I will explain below.

    - Set the format for the cells to a standard currency or financial format.
    - Right click the sheet tab of the worksheet.
    - Select Show Code from the popup menu.
    - Copy the following code into the module that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A1:A10"))
    oCell = oCell * 1000
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    - Replace both occurrences of A1:A10 with the range you want to have multiplied by 1,000 automatically.
    - Switch back to Excel.

    The downside of this code is that you cannot easily edit existing values. If you enter 1.3, this will be changed to 1,300. If you then edit this to 1,400, this input will be multiplied by 1,000 again, resulting in 1,400,000. So instead of editing the existing value, you must enter 1.4; this will be converted to 1,400.

  11. #11
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    Hans:
    I hope I'm not exceeding your patience with this follow-up.

    The code works (thanks very much) but only after I enter the number in the cell, click into any other cell and click back into the original cell. For example, when I type 1.3 into a cell in the code range, then Enter; the cell shows 2; I click out of the cell then back into it and it shows $1,300.

    I've reviewed your instructions and think I followed them accurately. Also, I am actually in Excel 2003 and not 97, if that is relevant. Is there a way to have the final number show without having to exit and reenter the cell?

    Many thanks,
    Susan

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

    Re: Number Display (Excel 97)

    You can click the green check mark to the left of the formula bar to confirm data entry without moving to another cell.

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

    Re: Number Display (Excel 97)

    It sounds like you put Hans code into the wrong event routine. The behavior you describe would be the case if you put his code in the Worksheet SELECTION Change event routine. The code must go in the Worksheet Change event routine. Those are easy to get confused.
    Legare Coleman

  14. #14
    Star Lounger
    Join Date
    Oct 2002
    Location
    Bluffton, South Carolina, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Display (Excel 97)

    I had entered the code into the wrong place and moving it rectified the problem. Many thanks.

Posting Permissions

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