Blue Flower

Find and Replace Superscript or Subscript in Excel

How to find and replace a certain number with Subscript or Superscript in an Excel cell

As you know in Excel, you can use Find & Replace to replace some data with other data. When it comes to Subscript / Superscript this option in Excel is not easy to do. If you type in the Find / Replace dialog box in the Find what ?: number 2 field in the Replace with: 2 field and Format => Font => Superscript, Excel will format the entire cell as Superscript, which does not match us as a result.

Find and Replace Superscript and convert the number to a potentiation sign

Using the ASCII codes for 437 and 850 you can use the Find / Replace method in the following way.

Take for example that we need the text number m2 (square meter) in the Excel cell, convert number 2 to the Superscript to make the number 2 a form or potentiation sign.

  1. Select the cells in which you want to convert the number to Superscript.
  2. Start the Find / Replace dialog box by pressing the CTRL + H key combination
  3. Click the Options button to expand the dialog box
  4. In the 'Find what' field: do not enter anything, just click the mouse inside
  5. In the 'Replace with' field: click the mouse and press the combination ALT + 253 (or 0253 number on the keypad number) in some cases it is a combination of ALT + 0178 or Alt + 178.
  6. Click the Replace or Replace All button

If it is over, you are lucky, but if you do not then you have to use another Find / Replace method in Excel.

Find and Replace Number with Superscript via VBA macro

About VBA macros I've already written.
The simple solution to this problem is using the VBA macro in Excel. In the picture below you notice the first range in which we want all cells with text that contain m2 to be replaced with the "visual mark" Square Meter in which number will be a sign of potentiation. In the same image, see the completed results after launching the VBA macros (marked with red lettering).
So this VBA macro below puts Superscript no matter what text and what parts of the text and cell numbers it contains.

How to convert or replace number with superscript

For the solution of this Excel example, the VBA macro was used below. Attention! You must allow the VBA to run in the Excel you are using.

NOTE: After you start the VBA macros you will not be able to go back the step, ie there is no UNDO button. So you're all working on a copy of your Workbook.

Sub ChangeToSuperScript()
Dim X As Long
Dim Position As Long
Dim c As Range
For Each c In Range("A1:A100") 'range of cells
Position = InStr(c.Value, "m2") 'change the number 2 into a potentiation sign
If Position Then c.Characters(Position + 1, 2).Font.Superscript = True
End Sub

  1. Right-click on the worksheet name (Sheet1) in which you want to find / replace the Superscript.
  2. Click View Code (or ALT + F11)
  3. Copy this VBA code above to the right part of the VBE window of the respective Sheet1
  4. Save the workbook (* .xls or as * .xlsm) and close it, then open it again.
  5. Press ALT + F8
  6. Select the command name and then click on the RUN button to launch the VBA macroprocess

Note the changed numbers 2 in the form Superscript format. You can set a button to run VBA macros.

Example 2 - Find-Replace Superscript

In this Excel example, you will notice the VBA macro that will find the exact data m3, ie condition, and convert the number to Superscript. This VBA macro ignores other cells that contain 'm3' as part of the text.

Find replace method to change superscript in excel

If you use this VBA macro below, then you must select the cell range before starting VBA macros.

Sub ConvertToSuperScript2()
Dim c As Range
For Each c In Selection.Cells
If c.Value = "m3" Then 'two characters to change in three characters and so on
c.Characters(2, 1).Font.Superscript = True 'SUPERSCRIPT for two characters and the last character is converted m2, m3
End If
End Sub

Then, below, see another VBA macro that finds and replaces a certain character in the text in Superscript. In this VBA macro below, notice that Excel will, after launch, edit all the cells in the 'A1:A100' cell range for all the cases found in the commands. This VBA macro finds all the conditions we set (m2, m3, 1st) and converted them as Superscript (but the last sign we need)

Sub SuperScriptChange()
Dim X As Long, NumPosition As Long, Cell As Range
For Each Cell In Range("A1:A100")
NumPosition = InStr(Cell.Value, "m2")
If NumPosition = 0 Then
NumPosition = InStr(Cell.Value, "m3")
If NumPosition = 0 Then
NumPosition = InStr(Cell.Value, "1st")
If NumPosition = 0 Then
For NumPosition = 1 To Len(Cell.Text)
If Mid(Cell.Value, NumPosition) Like "#th*" Then Exit For
End If
End If
End If
If NumPosition Then
Cell.Characters(NumPosition + 1, 2).Font.Superscript = True
End If
End Sub

Find and Replace the Subscript in Excel

In the same way using the VBA, we can find and replace all the numbers that need to be like the Subscript.
Eg. if we use chemical formulas so if we need to use the Subscript for H2O, CH4, C2H4, CO2 etc. Be sure to select the range before starting VBA macros.

Convert number in text to Subscript

Sub ChangeToSubscript()
Dim i As Long
Dim rCell As Range
For Each rCell In Selection
For i = 1 To Len(rCell)
If IsNumeric(Mid(rCell, i, 1)) Then rCell.Characters(Start:=i, Length:=1).Font.Subscript = True
Next i
Next rCell
End Sub

VBA source: Internet Network