Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    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: 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. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #4
    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: 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. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    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: 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. #8
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #10
    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: 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. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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
  •