Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Background

In Excel 2010, for some ridiculous reason, there is no built-in hotkey (or even a button on the toolbar) for subscripting/superscripting text within a text cell.

You can, however, highlight the text, right-click the selection, click format, and then check the [x] subscript or [x] superscript checkbox.

Question

Are there any kinds of excel macros or workarounds to map two keyboard hotkeys to the subscript and superscript keys, respectively?

(It should only be, like, two lines of code - one for the event handler and one for the actual procedure call... I would write one myself but my VBA is rusty, at best, and I am pretty confident there is probably already some kind of solution, despite my inability to find one via search engine)

Thanks for any help you can provide!

share|improve this question
    
MS Word actually has a shortcut key for it... I don't know why they didn't think it would be useful in Excel. (in which people FREQUENTLY use subscripts/superscripts for variable names, etc.) –  advs89 Apr 7 '11 at 0:21

5 Answers 5

I usually save the website I get these from, but I took the bulk of this code from a forum ages ago...I suggest setting this macro to a hotkey. The comments at the top should be self-explanatory

    Sub Super_Sub()
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' If the characters are surrounded by "<" & ">" then they will be subscripted
' If the characters are surrounded by "{" & "}" then they will be superscripted
'
Dim NumSub
Dim NumSuper
Dim SubL
Dim SubR
Dim SuperL
Dim SuperR
Dim CheckSub, CheckSuper as Boolean
Dim CounterSub, CounterSuper as Integer
Dim aCell, CurrSelection As Range

For Each c In Selection
c.Select

CheckSub = True
CounterSub = 0
CheckSuper = True
CounterSuper = 0
aCell = ActiveCell
'
NumSub = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "<", ""))
    NumSuper = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "{", ""))
'
If Len(aCell) = 0 Then Exit Sub
If IsError(Application.Find("<", ActiveCell, 1)) = False Then
Do
    Do While CounterSub <= 1000
        SubL = Application.Find("<", ActiveCell, 1)
        SubR = Application.Find(">", ActiveCell, 1)
        ActiveCell.Characters(SubL, 1).Delete
        ActiveCell.Characters(SubR - 1, 1).Delete
        ActiveCell.Characters(SubL, SubR - SubL - 1).Font.Subscript = True
        CounterSub = CounterSub + 1
        If CounterSub = NumSub Then
            CheckSub = False
        Exit Do
        End If
    Loop
Loop Until CheckSub = False
End If
'
'
If IsError(Application.Find("{", ActiveCell, 1)) = False Then
Do
    Do While CounterSuper <= 1000
        SuperL = Application.Find("{", ActiveCell, 1)
        SuperR = Application.Find("}", ActiveCell, 1)
        ActiveCell.Characters(SuperL, 1).Delete
        ActiveCell.Characters(SuperR - 1, 1).Delete
        ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Superscript = True
        CounterSuper = CounterSuper + 1
        If CounterSuper = NumSuper Then
            CheckSuper = False
            Exit Do
        End If
    Loop
Loop Until CheckSuper = False
End If
'
Next

End Sub
share|improve this answer
    
To elaborate a little bit...if the cell had the text "T<e>st{i}ng M<a>c{r}o" and then ran the macro while the cell was selected, it the "e" and "a" would be subscripted, while the "i" and "r" would be superscripted –  MattyB Nov 30 '12 at 23:04
    
as an added bonus...or maybe useless prattling...another function missing from Excel that exists in Word... Sub Proper_Case() ' 'This will make the first letter of the text within any selection of 'cells uppercase. ' For Each x In Selection x.Value = Application.Proper(x.Value) Next End Sub –  MattyB Nov 30 '12 at 23:07

I just added to the code provided by ScottS so "^" or "_" can be used to precede characters. Note that ALL following characters will be sub/super-scripted if you use these characters. For example, Q_in (m^3/s) will not display correctly, you will need to use ScottS's syntax for this: Q< in> (m{3}/s). The code here will work for ScottS's syntax but also includes "_" and "^" options, such as Q_in or Q_supply gas where "supply gas" is subscripted.

For those who are unfamiliar with macros: If you don't have a "Developer" tab in Excel, you need to enable it and save worksheet as a macro-enabled worksheet. Office button (upper left circular button) > click "Excel Options" in lower right > viewing the "Popular" tab check "Show developer tab in ribbon"

Then you'll need to add this macro: "Alt+F11" then "Insert" > "module" and paste the code below. You can set a keyboard shortcut by hitting "Alt+F8" while viewing spreadsheet or click on "Macros" button in "Developer" tab. Select/highlite this macro (Super_Sub_mod) and click "Options..." this is where you can set a shortcut starting with "Ctrl" such as "Ctrl+j" by simply typing "j" in the box.

The changes aren't automatically made just because you have the proper syntax. You must select individual or multiple cells after writing them with the "_" "^" "{text}" "< text>" syntax then run the macro.

    Sub Super_Sub_mod()
'
' Keyboard Shortcut: set in "options" of macro window (alt+F8 in spreadsheet view)
'
' If the characters are preceded by an underscore "_" then they will be subscripted
' If the characters are preceded by "^" then they will be superscripted
'
Dim NumSub
Dim NumSuper
Dim SubL
Dim SubR
Dim SuperL
Dim SuperR
Dim CheckSub, CheckSuper As Boolean
Dim CounterSub, CounterSuper As Integer
Dim aCell, CurrSelection As Range

For Each c In Selection
c.Select

CheckSub = True
CounterSub = 0
CheckSuper = True
CounterSuper = 0
aCell = ActiveCell
'

'Subscripts
'all following "_"
If Len(aCell) = 0 Then Exit Sub
If IsError(Application.Find("_", ActiveCell, 1)) = False Then
NumSub = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "_", ""))
Do
    Do While CounterSub <= 1000
        SubL = Application.Find("_", ActiveCell, 1)
        SubR = Len(ActiveCell)
        ActiveCell.Characters(SubL, 1).Delete
        ActiveCell.Characters(SubL, SubR - SubL).Font.subscript = True
        CounterSub = CounterSub + 1
        If CounterSub = NumSub Then
            CheckSub = False
        Exit Do
        End If
    Loop
Loop Until CheckSub = False
End If
'select region "<text>"
If IsError(Application.Find("<", ActiveCell, 1)) = False Then
NumSub = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "<", ""))
Do
    Do While CounterSub <= 1000
        SubL = Application.Find("<", ActiveCell, 1)
        SubR = Application.Find(">", ActiveCell, 1)
        ActiveCell.Characters(SubL, 1).Delete
        ActiveCell.Characters(SubR - 1, 1).Delete
        ActiveCell.Characters(SubL, SubR - SubL - 1).Font.subscript = True
        CounterSub = CounterSub + 1
        If CounterSub = NumSub Then
            CheckSub = False
        Exit Do
        End If
    Loop
Loop Until CheckSub = False
End If
'
'Superscripts
'all following "_"
If IsError(Application.Find("^", ActiveCell, 1)) = False Then
NumSuper = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "^", ""))
Do
    Do While CounterSuper <= 1000
        SuperL = Application.Find("^", ActiveCell, 1)
        ActiveCell.Characters(SuperL, 1).Delete
        ActiveCell.Characters(SuperL, SuperR - SuperL).Font.Superscript = True
        CounterSuper = CounterSuper + 1
        If CounterSuper = NumSuper Then
            CheckSuper = False
            Exit Do
        End If
    Loop
Loop Until CheckSuper = False
End If
'select region "{text}"
If IsError(Application.Find("{", ActiveCell, 1)) = False Then
NumSuper = Len(aCell) - Len(Application.WorksheetFunction.Substitute(aCell, "{", ""))
Do
    Do While CounterSuper <= 1000
        SuperL = Application.Find("{", ActiveCell, 1)
        SuperR = Application.Find("}", ActiveCell, 1)
        ActiveCell.Characters(SuperL, 1).Delete
        ActiveCell.Characters(SuperR - 1, 1).Delete
        ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Superscript = True
        CounterSuper = CounterSuper + 1
        If CounterSuper = NumSuper Then
            CheckSuper = False
            Exit Do
        End If
    Loop
Loop Until CheckSuper = False
End If
Next

End Sub
share|improve this answer

You can't run a macro while on the "Cell Edit Mode" (cfr. http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/3333e18b-cef3-4d78-b47a-6916a1b2d84c/). Also, there are no ribbon buttons to do something like that. Your only chance seems to be this utility: http://www.panuworld.net/utils/excel/.

share|improve this answer

Assuming you want to highlight the text within the cell, and not just the selected text, create a macro with whatever hotkey you want and the following VBA:

ActiveCell.Font.Superscript = True
share|improve this answer

You can add the missing buttons of Superscript and Subscript by downloading the free software from http://www.panuworld.net/utils/excel/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.