Formating fractions in Excel

[sminabe]

Hi,
I know how to use the fraction format, but it displays the fraction as, for example,11 3/4, where the fraction (3/4) is displayed as the same font size as the integer, 11. What I want is for the fraction, 3/4, to be smaller font than the integer, 11, AND that the 3 be above the 4 not on the same horizontal level. I can't seem to show it in this text editor, but if you go into MS Word and type 11 space 3/4 you'll get exactly what I want, but its in Word not Excel. When I copy/paste the text from Word to Excel it display exactly as I want it BUT the 3/4 is inserted as a symbol not as text or a number.
Any help is appreciated

 

[sijpie]

You could write a user defined function (UDF) to achieve this.

It will need to look at the number, create the fractions (I don't think you can use an excel function to do that ) and then format the characters individually.

A UDF is nothing else then a macro that you give a special treatment if you want it to update the cell contents with every recalculation of the spreadsheet.

Your biggest challange will be to find a suitable algorithm to create your fractions, but I am quite sure you can find some with a search.

 

[slippednfell]

Hi,
I know how to use the fraction format, but it displays the fraction as, for example,11 3/4, where the fraction (3/4) is displayed as the same font size as the integer, 11. What I want is for the fraction, 3/4, to be smaller font than the integer, 11, AND that the 3 be above the 4 not on the same horizontal level. I can't seem to show it in this text editor, but if you go into MS Word and type 11 space 3/4 you'll get exactly what I want, but its in Word not Excel. When I copy/paste the text from Word to Excel it display exactly as I want it BUT the 3/4 is inserted as a symbol not as text or a number.
Any help is appreciated

This is a challange I'm facing at work with a spreadsheet. I have formatted the cells to be fractions, but I know nothing about writing code or macros. Can someone explain how I achieve the end result in fairly simple terms or steps? Any help would be greatly appreciated.

 

[sijpie]

I don't think this is an easy one. I am very busy at the moment, will try and give you a hand. If you can do some searching to find a calculation method to turn 3.875 into 3 7/8, then I can help you put that into a macro which also formats the 7 to superscript and 8 to subscript.

 

[slippednfell]

Thank you for any help you can give to me. I greatly appreciate it. I fear that not only do I need help with putting it into a macro that formats the fraction(s) correctly, I will need instruction on creating and running a macro as well.

I thought to convert a decimal into a fraction, just formatting the cells to fraction would do the trick, but I will have to try it at work tomorrow. If that fails, I will do research over the weekend to find something that will work.

 

[sijpie]

If you use the excel formatting to get the decimals, then you are stuck with the excel presentation of 3 7/8

But I assume you want 3 7/8 with the 7 being hiugher than the 8.

For this I need to have a string to manipulate. So you will need to find a formula (I know they exist, just don't heve the time to research) that does this conversion.

 

[sijpie]

OK, there is a real problem here.

I can make tis work with excel numbers formatted as fraction. But the only way to format it with super & subscript is to convert the number to text, and so it stops bing a number - ie formulas depending on this value return an error.

also I can't get it do do this automatically you would need to select the cell and run the macro to do this, for instance. So not a 'neat' solution. But if it is pure for visuals then it works fine.

Macro:

Sub FracScript()
   Dim ssstart As Integer, ssfinish As Integer
   ActiveCell.Value = "'" & CStr(ActiveCell.Text)
   ssstart = InStr(ActiveCell.Value, " ") + 1
   ssfinish = InStr(ActiveCell.Value, "/")
   ActiveCell.Characters(start:=ssstart, Length:=ssfinish - ssstart).Font.Superscript = True
   ActiveCell.Characters(start:=ssfinish + 1, Length:=Len(ActiveCell.Value) - ssfinish).Font.Subscript = True
End Sub

put this code in a macro module.
enter your number, format it as fraction (or enter the number as a fraction). select the cell and run the macro.

 

[slippednfell]

Thank you very much. I did hunt for formulas over the weekend and wasn't able to find anything useful. It is purely for visual, so I will give it a try by running a macro in each cell.

Thank you very much for your help and hard work. I very much appreciate it.

 

[sminabe]

Hi All,
Thanks for following up on this.
I just tried the macro from sijpie and it works well for what I need as text.

If anyone figures out how to maintain the numerical representation let me know.

Thanks again for the support