How do I make excel show fractions as decimals? Formatting column as Number does not work.

[ManDrone]

I have a macro for importing data from an XML file and converting it to CSV (after some manipulation, amputation etc.). One field represents the odds of something happening. This means the field contains mostly integers and some fractions. I want all fractions to display (and save in .csv) as decimals w/2 sig figs. In other words, if the field right now displays as 3/5 I want it to convert to 0.60.

I have indicated that the column should be Numeric. I thought this would make 3/5 turn into 0.60 but it does not, as nothing changes. I figured out that it only changes if I DOUBLE CLICK on the fractional value (??!?) after setting the column as a Number. Note that I change nothing in the cell, just double click and then move away.

How do I, within a macro, tell excel I want junk like 1/5 to render as 0.20?

 

[hiker95]

ManDrone,

What version of Excel are you using?

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste

 

[Andrew Poulsom]

Try this after formatting the cells as number with 2 decimals and selecting the cells:

Sub Test()
    Dim Cell As Range
    For Each Cell In Selection
        Cell.Value = Cell.Value
    Next Cell
End Sub

 

[ManDrone]

The macro you provided works. Thank you. One nitpick is it's awfully slow - I wish I knew a way to affect all values in the column in one fell swoop. Or that Excel listened to me when I said "hey jerk - when I say "number" change to number".

Using Excel 2007 FYI.

Thanks for the help.

 

[diddi]

if you have written a macro to do all your stuff, then find the line which places the data back to the cell and add a format:

eg

    Cells(myrow,mycol).value=Format(Myfraction,"0.00")

if this doesnt work try

    Cells(myrow,mycol).value=str(Format(Myfraction,"0.00"))

also check the column format in the sheet and make sure it is not forcing the format, but i am pretty sure that vba format will override sheets formating