Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coercing XL into decimal places (Excel 2000)

    I know this belongs in the realm of the FAQ of formatting cells in Excel. But I've come across a situation in which Excel simply refuses to write a number showing two decimal places. I'm extracting data from a cell into a variable called entry and writing it to another cell. After checking for "numeric" I execute (in desperation, having tried everything else I can dream up) this statement:
    entry = CStr(FormatNumber(entry, 2))
    I then write "entry" into another cell, which is pre-formatted as Number with 2 decimal places. But no joy <img src=/S/sad.gif border=0 alt=sad width=15 height=15>. A 3 will still appear as a 3, rather than a 3.00; a 2.4 will be 2.4, not 2.40. Is there a way to do it? Getting desperate <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>.

    thanks

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coercing XL into decimal places (Excel 2000)

    The problem is with the cstr function. You are assigning a string to a cell.

    The way to do this would be:

    If you want to keep all decimals, but only show 2:

    Sub test()
    Dim dNumber As Double
    dNumber = 3.141592654
    ActiveCell.Value = dNumber
    ActiveCell.NumberFormat = "0.00"
    End Sub

    Or if you want to remove all extra decimals and just have a number with two decimals in the cell:

    Sub test()
    Dim dNumber As Double
    dNumber = 3.141592654
    ActiveCell.Value = Val(Format(dNumber, "0.00"))
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coercing XL into decimal places (Excel 2000)

    I'm afraid this doesn't work either Jan. It appears that the "problem" cells (supplied in their own workbook) have some sort of formatting I can't identify. They are right-aligned, while those around them are left-aligned. The cell formatting dialog gives no hint as to what might be different, and changing them to left-aligned makes no difference.

    When I look on the same source sheet, I see values like 3.20. This is how they appear in both the cell and the formula bar. When I try to type 3.20 into one of these offending (offensive) cells, it will revert to a 3.2 in both cell and formula bar. Same with 5.00 becoming 5. I believe the source sheet might have been generated by pasting values from another sheet, but I'm unsure of details.

    I've attached a typical column. You'll notice that some numbers are blighted by this e.g. rows 48, 49, while others aren't e.g. rows 29, 30. I'd really like, not only a solution to this, but an explanation. Thanks for any brainwaves.

    Alan

    BTW, the use of CStr() was really just a last ditch desperate effort... not what I'd usually do.

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

    Re: Coercing XL into decimal places (Excel 2000)

    This often happens if values are imported from another application. Some cells will be seen by Excel as text, although they contain numbers. To remedy this:
    1. Select an empty cell and copy it to the clipboard.
    2. Select the entire range (including "good" and "bad" cells)
    3. Select Edit | Paste Special..., click Add, then OK.
    To get the formatting right:
    4. Select Format | Cells, set Horizontal Alignment to general.
    5. Set the Number format to Number, 2 decimal places.

  5. #5
    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: Coercing XL into decimal places (Excel 2000)

    I don't know how you put the "numbers" into the cells: most likely thru some text input, but excel has then as text ("that looks like a number") instead of a number. The right-alignment is an indication of this. Text is right aligned (when no explicit alignment) and numbers are right -aligned (with no explicit -alignment).

    To convert: there are several tricks:
    1) If all in 1 column, Highlight the column
    select data - text to column, delimited <finish>

    2) with mult ranges enter a 1 into a cell
    select the 1 and ctrl-c to copy
    highlight the range of data
    edit -pastespecial - multiply

    3) you can always also use the VALUE function, but this will involve creating an intermediate column and then paste special - values over the original, then delete the int column, so it is not as good as the other 2.

    This situation usually arrives via import of text, that excel gets confused and thinks is text. It generally happens to an entire column, not just certain cells.
    Steve

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coercing XL into decimal places (Excel 2000)

    Thanks to all you guys - Jan, Hans & Steve. Through a laborious combination of the methods you suggested, I have managed to get the several thousand data entries looking and behaving as they should. God knows what the original data entry personnel managed to do to the spreadsheets they sent me, but coersion was certainly the name of the game to set things right. Anyway, I now have some utility macros at hand to make things easier if (when) they do it again!

    thanks again

    Alan

Posting Permissions

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