Clean Up Superscript & Subscript Text in Excel Data

Dealing With SuperScripts/Subscripts

Sometimes you receive data or copy data that has superscripts or subscripts that you really don’t want to be included. This article will explore three actions that can be automated throughout your cell selection via VBA code.

1. Deleting all superscripts or subscripts from cells
2. Removing the superscript or subscript formatting from cells
3. Converting the last character of each cell to a superscript/subscript (based on criteria)

Keep in mind as you work through this article that the “scripting” is done at a character level. Hence, the most of VBA code written will be looping through characters within each cell. This is similar to how you would manually apply a super/subscript (selecting the specific character(s) and applying the format).

Deleting Superscripts/Subscripts

If you’ve imported or copied data into your spreadsheet that contains superscripts or subscripts, you may want to remove them prior to doing analysis or further manipulation. This can be a very tedious and manual task. That is why I created the following VBA code to loop through all cells in a currently selected range and wipe out those superscripts and subscripts.

Sub DeleteFontScripts()
'PURPOSE: Delete any superscripts/subscripts from selected range

Dim cell As Range
Dim x As Long

'Ensure a cell range is selected
  If TypeName(Selection) <> "Range" Then Exit Sub

'Optimize Code
  Application.ScreenUpdating = False

'Loop through each cell in User's Selection
  For Each cell In Selection.Cells
    
    'Loop through each character in cell value
      For x = 1 To Len(cell.Value)
        
        'Test the character for the superscript/subscript property
          With cell.Characters(x, 1)
            If .Font.Superscript = True Or .Font.Subscript = True Then
              .Delete
              x = x - 1
            End If
          End With
      Next x
  
  Next cell

End Sub

Remove Superscript/Subscript Formatting

If you are looking to simply remove any and all characters from a superscript or subscript state, you can simply toggle the property off for the entire selected range.

Sub FontScript_Remove()
'PURPOSE: Remove any Superscript/Subscript formatting from selected range

'Ensure a cell range is selected
  If TypeName(Selection) <> "Range" Then Exit Sub

'Remove Superscript formattting from cells
  Selection.Font.Superscript = False

'Remove Subscript formatting from cells
  Selection.Font.Subscript = False

End Sub

Adding Superscript/Subscript To Cell Text

It can be really annoying to have to highlight specific characters, go into the Font settings, and check the Superscript or Subscript checkboxes. What if you could simply type out your superscript/subscript and click a button to apply the format? Here are a couple of VBA macro options you could utilize to accomplish this.

VBA Macro #1

The below VBA code will test each cell in the user’s selected range to see if the last two characters of text are numbers or capitalized letters. If either case is true, the character(s) will be formatted as a superscript. You can easily substitute the Superscript property with Subscript property to get the converse effect.

Sub AutoSuperscript()
'PURPOSE: Add superscript to last two characters if numerical or capitalized

Dim cell As Range

'Ensure a cell range is selected
  If TypeName(Selection) <> "Range" Then Exit Sub

'Optimize Code
  Application.ScreenUpdating = False

'Loop through each cell in User's Selection
  For Each cell In Selection.Cells

    'Superscript last two characters (if numeric/capitalized)
      If IsNumeric(Right(cell.Text, 2)) = True Then
        cell.Characters(Len(cell) - 1, 2).Font.Superscript = True
        
      ElseIf IsNumeric(Right(cell.Text, 1)) = True Then
        cell.Characters(Len(cell), 1).Font.Superscript = True
        
      ElseIf Right(cell.Text, 2) Like "*[A-Z]*" Then
        cell.Characters(Len(cell) - 1, 2).Font.Superscript = True
        
      ElseIf Right(cell.Text, 1) Like "*[A-Z]*" Then
        cell.Characters(Len(cell), 1).Font.Superscript = True
        
      End If

  Next cell
  
End Sub

Here is a before/after displaying how this macro performs in action:

VBA Macro #2

The below VBA code will test each cell in the user’s selected range to see if the last two characters of text are not lowercase alphanumeric characters. If found true, the character(s) will be formatted as a superscript. This will better handle special characters (ie asterisk) that you would wish to superscript.

Sub AutoSuperscript_Alternative()
'PURPOSE: Add superscript to last two characters if not lowercase

Dim cell As Range

'Ensure a cell range is selected
  If TypeName(Selection) <> "Range" Then Exit Sub

'Optimize Code
  Application.ScreenUpdating = False

'Loop through each cell in User's Selection
  For Each cell In Selection.Cells
  
    'Superscript last two characters (if not lowercase)
      If Not Right(cell.Text, 2) Like "*[a-z]*" Then
        cell.Characters(Len(cell) - 1, 2).Font.Superscript = True
        
      ElseIf Not Right(cell.Text, 1) Like "*[a-z]*" Then
        cell.Characters(Len(cell), 1).Font.Superscript = True
        
      End If
      
  Next cell
  
End Sub

Here is a before/after displaying how this macro performs in action:

I Hope This Helped!

Hopefully, I was able to explain how you can use VBA code to apply and remove superscript/subscript formattting. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

VBA To Remove/Delete Superscripts From Excel Cell Text

What This VBA Code Does I have run into a couple of instances recently where I needed to clean up...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.