How to quickly find and replace character with superscript in Excel?
Find and replace with superscript by Find and Replace function
Find and replace with superscript by VBA
Find and replace with superscript by Find and Replace function
Actually, you can use Find and Replace to find the text and then replace with superscript format.
1. Select the range you want to find and replace work for, and press Ctrl + H to open the Find and Replace dialog.
2. In the Find and Replace dialog, click Replace tab, and then enter the text you want to find into the Find what textbox. See screenshot:
3. Then go to Replace with textbox, enter the text you want to replace with, take notice, hold Alt key and then press 0179 keys on the Number board to enter the superscript number 3. See screenshot:
4. Click Replace All. And a dialog pops out to remind you how many replacements have been done. See screenshot:
Tip: But with this method, you only can superscript number 2 by pressing 0178, and 3 by 0179.
Find and replace with superscript by VBA
With Find and Replace function, only can replace the number with superscript. If you want to replace letter with superscript as below screenshot shown, you can apply VBA code to solve it.
1. Press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below VBA code to the script.
VBA: Find and replace with superscript format
Sub DoConvert()
'UpdatebyExtendoffice20160705
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("Select a range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
For Each xCell In xRg
If xCell.Value = "YL3" Then
xCell.Characters(1, 1).Font.Superscript = True
End If
Next
End Sub
3. Press F5 key to run the code, and a dialog pops out to remind you select a range. See screenshot:
4. Click OK, the specific character in specific string has been converted to be superscript format in the selection. See screenshot:
Tip: in the code, “YL3” is the string you want to find from, and (1, 1) indicates to convert first 1 character of the string, if you want to convert first two character, you can change it as (1, 2), if you want to convert 1 character from second character of the string, you can change it as (2, 1).
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
