CODEWITHSUNDEEP
excelvbaremoveallsuperscript
user7095638
user7095638

Reputation: 13

Remove superscripts in Excel in VBA

How to remove characters that are written in superscripcts in an Excel file (.xlsx) using VBA?

They appear only in of some cells and I would like to leave other parts of thise cells (not written in supersripts) unchanged.

Copying text into other programme forgets that some letters were in superscripts, so it most probably has to be done in Excel.

Upvotes: 0

Views: 1947

Answers (1)

tjb1
tjb1

Reputation: 757

Code takes a little bit to run but I've included code to delete the superscript or "normalize" it.

The following takes superscript characters and deletes them:

Sub DeleteSuperScript()
    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In ActiveSheet.UsedRange
        For i = 1 To Len(c)
            If c.Characters(i, 1).Font.SuperScript Then
                c.Characters(i, 1).Delete
                i = i - 1
            End If
        Next i
    Next c

    Application.ScreenUpdating = True

End Sub

The following takes superscript characters and "normalizes" them:

Sub NormalizeSuperScript()
    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In ActiveSheet.UsedRange
        For i = 1 To Len(c)
            If c.Characters(i, 1).Font.SuperScript Then
                c.Characters(i, 1).Font.SuperScript = False
            End If
        Next i
    Next c

    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions