Superscript after decimals

[Jaxfruit]

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).

 

[dcardno]

I don't know of any way to format cells that way, but you can automate the process you are undertaking a little bit:

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.

 

[Jaxfruit]

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]

Does this modified version of Dean's code do what you want...

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]

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]

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]

After the whole number would be great.

Jax

 

[dcardno]

Jax, this will superscript both the decimal point and the decimal value (including zero, for exact-value temperatures).

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]

I think this should do it...

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

 

[Jaxfruit]

The last one from Rick is what I will use.
Thanks a lot guys. Good help.

Jax