Entering fractions in Excel can be confusing. When a fraction is entered, Excel converts the value to a date format e.g. 1/2 is stored as 01-Feb.
This is frustrating and without storing the value correctly as a fraction, any formulas dependent upon this value will not work correctly.
To enter a fraction in Excel, type a 0 and a space followed by the fraction. Excel then stores the number as a fraction and reduces it to the smallest possible denominator. So if you type 0 2/16, Excel stores it as 1/8.
Now with a fraction stored correctly the cell can be referred to within formulas without error. Further formatting can also be applied to the cell through the Format Cells dialogue box.
If you have entered a number as a decimal, this can easily be converted to a fraction by using the Number format list. If you enter the number 0.25, or it is returned as the result of a formula, it can be formatted as a fraction to get 1/4.
I was having a major problem copying fractions from a webpage and pasting into excel (2003) with out them being converted in to date.
My solution is a bit tricky, but works great.
1) Determine which column(s) will hold the fractional values.
2) Format the entire column(s) as Text.
3) Paste in the data as unicode text (paste special)
4) Select all newly pasted fractions and format as fraction of your choice OR with fractions selected do step 5
5) run the following macro. Formats cells as 2 digit denominators unless changed. Code follows:
Public Const VK_F2 = 113
Public Const VK_ENTER = 13
Public Const KEYEVENTF_EXTENDEDKEY = &H1
Public Const KEYEVENTF_KEYUP = &H2
Public Declare Sub keybd_event Lib “user32.dll” (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Sub FIX_FRAC()
‘Ted Young 10/21/2012
For Each cell In Selection
cell.Value = WorksheetFunction.Trim(cell.Value)
cell.NumberFormat = “# ??/??”
Next cell
Call F2_ENTER
End Sub
Sub F2_ENTER()
For Each cell In Selection
keybd_event VK_F2, 0, 0, 0
keybd_event VK_F2, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_ENTER, 0, 0, 0
keybd_event VK_ENTER, 0, KEYEVENTF_KEYUP, 0
Next cell
End Sub
As an afterthought, when you get to step 4 of my solution, you’ll find the formatting to fraction doesn’t quite “take” (excel will right-hand align numbers). You would have to open each cell containing a fraction by double clicking or hitting F2, then close it by clicking another cell or hitting “Enter”.
Just run the macro – it does it all for you.
BTW (for VBA coders), the F2_ENTER subroutine is another workaround of mine because I found that VBA “Sendkeys” no longer works on most machines running Vista or Windows 7 because of UAC (User Account Controls).
The “keybd_event” method is 6 times as much code as “Sendkeys” is, but it works. I did find I had to call it at the end of the subroutine or I would get some unpredicable results. As a stand-alone subroutine it’s fine.