Photo by Melody
Congratulations to all our photo contest participants! Check out the winning photos here. We will have the 2015 calendars available to order from Zazzle soon.

Computer Talk: Superscript in Excel

Communities > Forums > Computer Talk
bookmark
Forum: Computer TalkReplies: 19, Views: 121
Add to Bookmarks
-
AuthorContent
Leftwood
Saint Bonifacius, MN
(Zone 4a)

January 4, 2010
6:37 PM

Post #7427169

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?

Rick
hcmcdole
Powder Springs, GA
(Zone 7b)

January 4, 2010
8:37 PM

Post #7427591

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()

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

End Sub
Leftwood
Saint Bonifacius, MN
(Zone 4a)

January 5, 2010
5:54 AM

Post #7429521

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?

Rick


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

Thumbnail by Leftwood
Click the image for an enlarged view.

hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
10:20 AM

Post #7429708

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.


treelover3
Minneapolis, MN
(Zone 5a)

January 5, 2010
7:40 PM

Post #7431199

Rick,
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.
Mike
hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
8:47 PM

Post #7431441

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).








treelover3
Minneapolis, MN
(Zone 5a)

January 5, 2010
8:52 PM

Post #7431464

hcmcdole,
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.
tl³
hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
8:56 PM

Post #7431478

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?

treelover3
Minneapolis, MN
(Zone 5a)

January 5, 2010
9:27 PM

Post #7431599

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.
tl³
hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
9:35 PM

Post #7431626

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.
hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
9:38 PM

Post #7431633

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.
hcmcdole
Powder Springs, GA
(Zone 7b)

January 5, 2010
9:45 PM

Post #7431655

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.

PotEmUp

PotEmUp
Fremont, CA
(Zone 9a)

January 5, 2010
11:56 PM

Post #7432237

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
Click the image for an enlarged view.

hcmcdole
Powder Springs, GA
(Zone 7b)

January 6, 2010
12:42 AM

Post #7432437

I think charmap is the same thing as insert symbol
hcmcdole
Powder Springs, GA
(Zone 7b)

January 6, 2010
3:12 AM

Post #7433096

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
Next
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
Leftwood
Saint Bonifacius, MN
(Zone 4a)

January 6, 2010
7:16 PM

Post #7435350

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!
Rick

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?
hcmcdole
Powder Springs, GA
(Zone 7b)

January 8, 2010
12:03 AM

Post #7439717

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.

Leftwood
Saint Bonifacius, MN
(Zone 4a)

January 8, 2010
3:01 AM

Post #7440295

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
Rick
hcmcdole
Powder Springs, GA
(Zone 7b)

January 8, 2010
3:28 AM

Post #7440428

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.



Leftwood
Saint Bonifacius, MN
(Zone 4a)

January 8, 2010
3:48 AM

Post #7440507

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

You cannot post until you register and login.


Other Computer Talk Threads you might be interested in:

SubjectThread StarterRepliesLast Post
Hotmail help Calalily 13 Jul 17, 2008 11:51 AM
Screen Capture Programs holty 16 Feb 8, 2010 2:13 AM
This 'problem' is driving me nuts! hczone6 10 Feb 11, 2014 4:24 PM
Laptop recommendations Gwendalou 10 Jul 30, 2007 10:12 PM
G Mail Invitations PudgyMudpies 43 Dec 28, 2007 12:12 AM


We recommend Firefox
Overwhelmed? There's a lot to see here. Try starting at our homepage.

[ Home | About | Advertise | Media Kit | Mission | Featured Companies | Submit an Article | Terms of Use | Tour | Rules | Privacy Policy | Contact Us ]

Back to the top

Copyright © 2000-2014 Dave's Garden, an Internet Brands company. All Rights Reserved.
 

Hope for America