# Thread: Format Fractions (Excel 2000)

1. ## Format Fractions (Excel 2000)

Is there a way to automatically change a fraction to superscript without changing the whole numbers in the same cell?

2. ## Re: Format Fractions (Excel 2000)

Not sure what you are after.
You can format the number to a fraction:
Format - cells - number (tab) - fraction
choose desired format

If you want to change part of the format of the cell, you would have to do it with a macro and the cell would have to be "text" not a number.

You could keep the "number" in an unformatted cell and then have a macro convert it a text fraction and then format as desired.

You could do it automatically using a "worksheet change" event macro [Do a search using (no quotes) "Worksheet_Change", for example code]

You would have to be more specific about what you want in the "formatting" before we could provide any particular code for that aspect.

Steve

3. ## Re: Format Fractions (Excel 2000)

Dea,

You may want to experiment with the "Equation Editor".
In XL 2002 go to: Insert | Object | Microsoft Equation 3.0

The actual file name is: EQNEDT32.EXE
The help file is: EQNEDT32.HLP

Regards,

Jim Cone
San Francisco, CA

4. ## Re: Format Fractions (Excel 2000)

Here is a scheme I alluded to (I had some time to play):

Add this code to module in VB:
<pre>Option Explicit
Sub FormatFraction(rng As Range, iRowOffset As Integer, iColOffset As Integer)
Dim sTemp As String
Dim lSpace As Long
Dim lDivide As Long
Dim rCell As Range

Set rCell = rng.Cells(1, 1)

With rCell.Offset(iRowOffset, iColOffset)
If Not IsNumeric(rCell.Value) Then
.Value = rCell.Value
Exit Sub
End If

sTemp = rCell.Text
lSpace = InStr(sTemp, " ")
lDivide = InStr(sTemp, "/")

If lSpace = 0 Or lDivide = 0 _
Or lSpace > lDivide Then
.Value = rCell.Value
Exit Sub
End If

.Value = "'" & sTemp
.Characters(Start:=lSpace + 1, _
Length:=lDivide - lSpace - 1).Font.Superscript = True
.Characters(Start:=lDivide + 1, _
Length:=Len(sTemp) - lDivide).Font.Subscript = True
End With
End Sub</pre>

when called giving a cell reference and the appropriate offsets it will take the formatted (explicitly defined format of the cell) and (if it yields a fraction) will superscript the numerator and subscript the denominator.

You can have it auto run by adding this code to the worksheet object of the sheet you want to do this with:
<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("A1:A10")
Dim rCell As Range
If Not Intersect(Target, rng) Is Nothing Then
For Each rCell In Intersect(Target, rng)
Application.EnableEvents = False
Call FormatFraction(rCell, 0, 1)
Application.EnableEvents = True
Next
End If
Set rCell = Nothing
Set rng = Nothing
End Sub</pre>

This will run whenever any changes are made to the values in the sheet. If the changes are made to A1:A10 the changed cells are examined and a copy of the format is put into column B of that row [Offset (0,1)]. This keeps the "values" in A and only puts the formatted "text" into B. If you do not need the actual value, you could replace the number you enter with the formattted text using Offset (0,0).

Steve

5. ## Re: Format Fractions (Excel 2000)

This works great, Steve, except it doesn't put the fraction in superscript. When I type the number 1.5 (for instance), it changes to 1 1/2, which is a wonderful thing. I want it to change the fraction to superscript, though, and leave the whole number alone. Am I doing something wrong?

6. ## Re: Format Fractions (Excel 2000)

I'm looking for Microsoft Equation in Excel XP (Insert/Object), but I don't seem to have Microsoft Equation. Any idea where I can find it to load it?

dea

7. ## Re: Format Fractions (Excel 2000)

Change the macro. I thought you wanted the numerator in super and the denominator in subscript to put the entire fraction in superscript :

Delete these 2 lines:
<pre> .Characters(Start:=lSpace + 1, _
Length:=lDivide - lSpace - 1).Font.Superscript = True
.Characters(Start:=lDivide + 1, _
Length:=Len(sTemp) - lDivide).Font.Subscript = True</pre>

and replace it with the line:
<pre> .Characters(Start:=lSpace + 1, _
Length:=Len(sTemp) - lSpace).Font.Superscript = True</pre>

This superscripts the entire fractional part.

Steve

8. ## Re: Format Fractions (Excel 2000)

Please forgive my ignorance. I regret that I'm not very good with macros. Why do I keep getting "Argument not optional" when I try to run this macro? Also, the code changes the .5 (or whatever) to a fraction, but it doesn't superscript the fraction. Do I run the macro before I do anything and then type the numbers?

dea

9. ## Re: Format Fractions (Excel 2000)

dea,

If you did a search in Windows Explorer for the file with no results then:
From the XP help file...

Install or remove individual components in Microsoft Office

If you originally installed your Microsoft Office program from a network file server or from a shared folder,
you must install or remove components from that location.

If you installed your Office program from a CD-ROM and you've mapped your CD-ROM drive to a new drive letter
since installing the Office program, reinstall from the CD-ROM.

If you're running any Office program files from the CD-ROM,
you must uninstall the Office program and then reinstall it from the CD-ROM.

Quit all programs.
Double-click the Add/Remove Programs icon in the Windows Control Panel.
Do one of the following:
For Windows 2000 and Windows Millennium Edition:

If you installed your Office program as part of Microsoft Office,
click Microsoft Office in the Currently installed programs box, and then click the Change button.

If you installed your Office program individually,
click the name of your program in the Currently installed programs box, and then click the Change button.

For Windows 98 and Windows NT 4.0:
If you installed your Office program as part of Microsoft Office,
click Microsoft Office on the Install/Uninstall tab, and then click the Add/Remove button.

If you installed your Office program individually,
click the name of your program on the Install/Uninstall tab, and then click the Add/Remove button.

Follow the instructions on the screen.
'---------------------------------------------------------

And for what its worth, you can enter fractions in Excel by
entering a 0 followed by a space followed by the fraction (the zero will not show) or
a number followed by a space followed by the fraction.

Regards,
Jim Cone
San Francisco, CA

10. ## Re: Format Fractions (Excel 2000)

You don't give me much to go on. It sounds like you changed the Line:
Call FormatFraction(rCell, 0, 1)
and removed one of the 3 required parameters. If so add the parameter back. The first is the range reference, the second the offset for the rows and the third is the offset for the columns.

If that is not it, you need to provide more info.

Did you setup and run the code as originally provided?
Were you able to add numbers to A1:A10 and have it change B1:B10? (with the fraction part super/part sub?)
Did you make the modifications to the code to superscript it all?
Were you able to add numbers to A1:A10 and have it change B1:B10? (with the fraction all superscript?)
Did you further modify the code? What changes did you make?
What line is causing the error message (select <Debug> when the error pops up to see the highlighted line)?

Steve

11. ## Re: Format Fractions (Excel 2000)

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

Dea,

This could be the answer...

http://www.j-walk.com/ss/excel/files/supersub.htm

Regards,
Jim Cone
San Francisco, CA

#### Posting Permissions

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