Superscript in Excel

Saint Bonifacius, MN(Zone 4a)

I use Excel 2000 with Windows 2000 pro. Is there a pre loaded shortcut for making superscripts?

Much like I can use control+i to change back and forth between regular font and italics within the same cell, I want to be able to do that with superscripts. I have been able to format the entire cell for superscripts, auto produce a superscript as the last character within a cell and perform individual resets within cell via the mouse (by shading, format, check/uncheck superscript box). But I want a shortcut key.

Macros have been fairly easy for me to set in the past, but I haven't been able to produce a macro for this. I don't know enough about editing macros to solve my dilemma, but I'm willing to learn.

Can I do that?


Powder Springs, GA(Zone 7b)

I don't know if there is a shortcut or not. In Word you can highlight the character(s) and then format font, click on superscript. I suspect Excel would be similar. You can also use the format painter tool.

As for macros, click on the record button, perform your action, and stop recording. Go look at the macro and make appropriate changes as you see fit.

Here is what the macro should look like: (I'm using Office 2007 but it should be similar in 2000). I deleted all the extra junk. I put x2 in cell A1 (meant to be x square). You can assign a hot key to the macro as well. Good luck.

Sub Macro1()

ActiveCell.FormulaR1C1 = "x2"
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Superscript = True
End With

End Sub

Saint Bonifacius, MN(Zone 4a)

Thanks for the help. I'm learning a lot now about VBA code. Your code did exactly what it is supposed to do, as I read it. (the pic below is the result) But unfortunately, it's not what I want.

-- I need the macro to be available anywhere on the sheet, so we can do without the ranges.
-- I don't want the font to match that of the active cell. It's to be always Tahoma.
-- Both regular font characters and superscripted numbers are variable, in both number of characters and the characters themselves.
For example, I might want one cell to read:
IHcool² IE¹²
and another:
Lilium martagon³

So the best I've done so far is:

Sub superscript()
' Macro7 Macro
' Macro recorded 1/4/2010 by Rick
' Keyboard Shortcut: Ctrl+t
ActiveCell.FormulaR1C1 = " "
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Tahoma"
.superscript = True
End With
End Sub

But that still changes the entire cell to superscripting. Am I misreading your example? I want to be able to use the macro multiple times within one cell. If that means one macro to change to superscript and a second to change back to the cell's original font, that would be fine.

Yes, the highlighting, then format, font, superscript with the mouse does work (and back again too), but I'd like to not bother with the mouse. Is it possible?


This message was edited Jan 4, 2010 11:55 PM

Thumbnail by Leftwood
Powder Springs, GA(Zone 7b)

You will have to modify the macro as needed. The code I provided was just for starters based on what limited information you gave in your original post.

The cell formula can be changed to the ActiveCell as you found out. Just eliminate the range("ColumnRow").select statement. Same for font name, font size, etc. but then you didn't ask for that in the original post.

The selection of characters to be formatted is in the Start:=xStart, Length:=xLength part which is fairly explanatory. The xStart is where to start in the string and the xLength is how many characters to select. I don't think you can do a multiple select on text though (maybe you can but I've never tried it). So you might have to do the same type of logic multiple times to do multiple superscripts in the same cell. The other thing you can do is eliminate the select in the code, let the user do the select, and then the hot key or run the macro (or put a command button on the sheet or a selection on the toolbar). If the location will always be the same then you can hard code the selects in your macro.

The other thing you can do is click in your cell and if all the numbers are to be superscripted then you would work across the string, selecting numbers only and applying the formatting. Or (and) if you want to superscript the last character then you would add in the logic to select that. You would just have to hit the hot key or a command button or tool on the toolbar. How's that for simplicity? Piece of cake?

Hope this is clear as mud.

Minneapolis, MN(Zone 5a)

Do you want to be able to make a superscript like this: ³ - within a cell?

Google "alt codes" and you should find lists of all sorts of 4-digit numbers that you can enter while holding down the alt key. The superscript ³ is alt plus 0179. I use this when I sign my signature here as tl³. Alt plus 0176 is the ° sign for °F or °C. There is also ® which is alt plus 0174 and ™ is alt plus 0153. There are many, many alt codes for making just about any special or numerical superscript symbol.

I'm not sure if this is exactly what you're looking for, but thought I would ask.

Powder Springs, GA(Zone 7b)

I am not sure you can insert special characters like that (using ALT plus a sequence) in Excel. You can insert symbols from a list of special characters though. For superscript it is a toss up on whether to go this route or just select, format, and choose superscript. Special characters have to use the insert symbol though (or copy and paste from somewhere else).

Minneapolis, MN(Zone 5a)

I just tried adding the superscript ³ after some numbers I keyed into a cell in Excel and it worked just fine. Since that worked, I would guess any alt plus number code would work.

Powder Springs, GA(Zone 7b)

Hmm, what version do you have? It is ALT, right? When I get to ALT+01, I get a dialog box to save or open (can't remember which one). Are you using a numeric keypad?

Minneapolis, MN(Zone 5a)

I am using Excel 2003 here at work. All alt numbers are 4 digit numbers and any leading zeros need to be keyed in while holding the alt key down.

If you want to enter "1" you have to type 0001 for it to work. I just tested alt plus 0001 and nothing is printed so that isn't a valid alt code.

Yes, I am using a numeric keypad on my keyboard, but I don't believe that should make a difference.

Powder Springs, GA(Zone 7b)

I think it does make a difference (or it used to). I will try it on my wife's laptop that has a numeric keypad plus her Excel is 2003. Mine is 2007.

Powder Springs, GA(Zone 7b)

Yep, it worked on her laptop but only with the numeric keypad. The normal numbers just beeped each time I hit them.

I can't even enter special characters in this post from my laptop.

Powder Springs, GA(Zone 7b)

Well now that I am on my desktop and I enter alt-0176 it does nothing.

If I use the numeric keypad I get this: ° but if I forget to turn num lock on then I go to my home page.

Fremont, CA(Zone 9a)

You can also use Charmap (Character Map) to find those Alt codes for various fonts. In Vista - put charmap in the search and hit enter. In XP I think it is in the run box.
There is a drop down for the various fonts. Click on any character and the alt keystroke shows in the lower right - Or select what you want and copy it to your spreadsheet (or any other windows program)
I have a small post it note next to the edge of my monitor for the few I use most: ° ¢ ¼ ½ ¾ ¶ © ⅝

Have a good day ☺

Thumbnail by PotEmUp
Powder Springs, GA(Zone 7b)

I think charmap is the same thing as insert symbol

Powder Springs, GA(Zone 7b)

Here is some more code that only superscripts numbers and periods. You could also add checks for other characters such as negative signs, engineering notation, etc.

Sub superscript()
For j = 1 To Len(ActiveCell)
If IsNumeric(Mid(ActiveCell, j, 1)) Or Mid(ActiveCell, j, 1) = "." Then
ActiveCell.Characters(j, 1).Font.superscript = True
End If
End Sub

This is most strange. I edited this post to add white space because it looked like it had been stripped but it was still there when I edit. I guess DG removes white space at the beginning of lines for display? Pretty silly.

This message was edited Jan 6, 2010 7:06 AM

Saint Bonifacius, MN(Zone 4a)

My problem had been that I could not apply a macro while typing in the cell. Thus the cell would be all superscript or all not, because I could only apply the macro in the beginning (or at the end by re activating the cell).

hcmcdole, your new suggested code works great! It saves me a whole wad of steps! Thank you!

I see that your if-then sentence is quite long. Yes, DG does remove spaces at the beginning of a line, and extra spaces in between characters in mid sentence.

Mike, alt codes do work in my Excel program, but there are only alt codes for superscript 1,2 and 3.

Thanks everyone!

P.S. I see there are bits and pieces of VBA code references all over the internet. Does anyone have a preferred one that they use?

Powder Springs, GA(Zone 7b)

You could substitute a variable for part of the code to simplify matters such as:

x = Mid(ActiveCell, j, 1))
If IsNumeric(x) Or x = "." Then
ActiveCell.Characters(j, 1).Font.superscript = True
End If

What do you mean by "Does anyone have a preferred one that they use?" VBA is Microsoft's standardization of macro code. In the past Word had its own macro language and Excel had its own, etc. So a programmer would have to learn all the different macro languages if they programmed for each application. Bill Gates said they needed one standard language and VB became the underlying language. The objects are different between each application but the concept is the same. It made life so much simpler going between Access, Excel, Word, etc. when doing macros.

Saint Bonifacius, MN(Zone 4a)

Yes, that works well.

From the little internet perusing I've done, I did get the idea that for word and for excel was different somehow. Thanks for clearing that up.

I was looking for a maybe better code list to refer to and learn from. I really don't know much about the whole language, but it bears a similarity to the (very) old Basic language. I thought maybe it's not that difficult. Actually, the built in help for macros in the excel program is fairly explanatory.

Thanks again

Powder Springs, GA(Zone 7b)

VB has come a long way from plain old Basic. It's great for doing forms and is fairly robust. If it isn't robust enough or something you can't do you can build a DLL in C++ and call it from VB (a bit awkward due to the way both languages use the stack but doable). It has been a while since I used to do this but the speed in algorithms came from 'C' and the forms all came from VB. The same can be applied to VBA but in this case you also use the power of Excel or any add in.

If you work in it for a while it becomes easy (easier at least). You know what you want to accomplish and you know the basic tools which is a good starting point. Then you add in controls or references to do the heavy lifting for you. Buy a book on using VBA for whatever app you are programming for at a local book store. It is a great help on getting started too.

Saint Bonifacius, MN(Zone 4a)

Always grateful with everything you have to say --- Rick

Post a Reply to this Thread

Please or register to post.

Upload Images to your reply

    You may upload up to 5 images