Macro for conditional superscripts in Excel

[phatcat43]

Hi there-
I often work with spreadsheets that need superscripts to identify statistical significance (with letters). Is there a way to create a macro that will convert all letters within highlighted cells into superscripts?

Thanks
david

 

[KWMSeattle]

Highlight the cells necessary and then run this macro:

Sub AddSuperScript()
 
    With Selection.Font
        .SuperScript = True
    End With
 
End Sub

 

[phatcat43]

Thanks, but its not quite what I'm looking for. I should have explained that the cells have both numbers and letters, and I want to keep the numbers as regular font and convert only the letters to superscripts.

 

[VoG]

Try

Sub SuperS()
Dim c As Range, iChr As Integer
For Each c In Selection
    For iChr = 1 To Len(c.Value)
        If Val(Mid(c.Value, iChr, 1)) = 0 Then c.Characters(Start:=iChr, Length:=1).Font.Superscript = True
    Next iChr
Next c
End Sub

 

[KWMSeattle]

Great solution, VoG! This was an interesting challenge. I love learning new tricks like this.

 

[Norie]

Perhaps this will give you a start.

Sub Macro1()
Dim c As Range
Dim rng As Range
Dim I As Long
    Set rng = Range("A1:A10")
    
    For Each c In rng
        For I = 1 To Len(c.Value)
            If UCase(Mid(c.Value, I, 1)) Like "[A-Z]" Then
                c.Characters(Start:=I, Length:=1).Font.Superscript = True
            End If
        Next I
    Next c
    
End Sub

 

[VoG]

Great solution, VoG! This was an interesting challenge. I love learning new tricks like this.

Thank you

Actually, this is somewhat more transparent

If Not IsNumeric(Mid(c.Value, iChr, 1)) Then c.Characters(Start:=iChr, Length:=1).Font.Superscript = True

 

[phatcat43]

Thanks, that works great except for one thing - it turns 0 into a superscript. Anyway to avoid that?

David

 

[VoG]

Thanks, that works great except for one thing - it turns 0 into a superscript. Anyway to avoid that?

David

Try

Sub SuperS()
Dim c As Range, iChr As Integer
For Each c In Selection
    For iChr = 1 To Len(c.Value)
        If Not IsNumeric(Mid(c.Value, iChr, 1)) Then c.Characters(Start:=iChr, Length:=1).Font.Superscript = True
    Next iChr
Next c
End Sub

 

[Norie]

Vog

Are you sure about that?

Your first code does seem to superscript 0 but the amendment you posted seems to sort that out.