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.

Ted Young says

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

Ted Young says

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.