Superscript after decimals

Jaxfruit

New Member
Joined
Jul 19, 2017
Messages
5
Good day all,

I was wondering if it is possible to format cells in such a way that decimal after the period automatically appear in superscript.
I am recording a temperature list where I would like to shown each temperature in degrees and superscript 10th of degrees.
Now I need to select and change the decimal manually (a lot of work).
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
536
Office Version
  1. 2013
Platform
  1. Windows
I don't know of any way to format cells that way, but you can automate the process you are undertaking a little bit:
VBA Code:
Sub Superscripter()

Dim cl As Range
Dim iDecimal As Integer

For Each cl In Selection
    iDecimal = Int(WorksheetFunction.Log10(cl.Value)) + 3
    cl.FormulaR1C1 = "'" & Trim(Str(cl.Value))
    cl.Characters(Start:=iDecimal, Length:=1).Font.Superscript = True
Next cl

End Sub
Select the range of cells in-which you want to superscript the decimal portion, and then run the code. I don't know whether you are working in °F or °C, so I generalized it at least to handle temperatures > 100°. Handling values < 0° is left 'as an exercise for the reader.'

You will have seen that you have to store the values as text to get them to accept character-level formats - this means you can't calculate averages, Max/Min, or graph them.
One late thought - I didn't test that all the decimal values are present - if you have exact degree readings and want a superscripted ".0" you will have to force the string derived from the cl.value to be in the form xx.0. I think you can do that with the form of the Str conversion - but this will get you started.
 
Last edited:

Jaxfruit

New Member
Joined
Jul 19, 2017
Messages
5
Hello Dean,

Thank for your reply.
This code works for me at the moment. It is not exactly what I was looking for, but workable for now.
Indeed the exact degree reading does not show a decimal with this code running.

Thanks Jax
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
Does this modified version of Dean's code do what you want...
VBA Code:
Sub Superscripter()

  Dim cl As Range
  Dim iDecimal As Integer
  
  For Each cl In Selection
    iDecimal = Int(WorksheetFunction.Log10(cl.Value)) + 2
    cl.FormulaR1C1 = "'" & Trim(Replace(Format(cl.Value, "0.0"), ".", ""))
    cl.Characters(Start:=iDecimal, Length:=1).Font.Superscript = True
  Next cl

End Sub
 

Jaxfruit

New Member
Joined
Jul 19, 2017
Messages
5

ADVERTISEMENT

Rick, it does in a way. the whole numbers are now showing a decimal "0" in superscript.
In your version the period sign disappears.

Both your codes (Rick and Dean) are nice to work with.

Jax
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
Did you want the period showing or not? If you want it, then where at... next to the whole number or in front of the superscripted number?
 

Jaxfruit

New Member
Joined
Jul 19, 2017
Messages
5

ADVERTISEMENT

After the whole number would be great.

Jax
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
536
Office Version
  1. 2013
Platform
  1. Windows
Jax, this will superscript both the decimal point and the decimal value (including zero, for exact-value temperatures).

VBA Code:
Sub Superscripter()

  Dim cl As Range
  Dim iDecimal As Integer
 
  For Each cl In Selection
    iDecimal = Int(WorksheetFunction.Log10(cl.Value)) + 2
    cl.FormulaR1C1 = "'" & Trim(Format(cl.Value, "0.0"))
    cl.Characters(Start:=iDecimal, Length:=2).Font.Superscript = True
  Next cl

End Sub

If you want the decimal point down low ("with the full degree value") change the iDecimal value calculated from "+2" to "+3" and change the "length parameter in the font changing line to 1
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
I think this should do it...
VBA Code:
Sub Superscripter()

  Dim cl As Range
  Dim iDecimal As Integer
  
  For Each cl In Selection
    iDecimal = Int(WorksheetFunction.Log10(cl.Value)) + 3
    cl.FormulaR1C1 = "'" & Format(Trim(cl.Value), "0.0")
    cl.Characters(Start:=iDecimal, Length:=1).Font.Superscript = True
  Next cl

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top