On Tue, 19 Apr 2011 21:55:33 -0400, Phrank wrote:

Hi,

Right now, the fastest way I've found to superscript part of text
within a cell is a 5 step process. I'm looking for a faster way?

The vast majority of times, the superscript will be the first or last
letter of a cell (for footnoting), but there are also times when
scientific compounds are seen in cells with superscripting or
subscripting, so it's not always the first/last character.

Here are the steps I currently use to do this:
- Select the cell
- Select the text in the formula bar that I need superscripted
- Right click and select Format Cells
- Select Superscript
- Click OK

What if I encapsulated that which I wanted superscripted or
subscripted? For example, my text in a cell might read '[a]Group
means are shown.' Then a worksheet event or some other macro could
process everything within the brackets to superscript (or use {} for
subscripting). I think something like that might work, wouldn't it?
Would this be able to be done with a Worksheet.Change event?

Thanks.

Frank

Here's one way, using a worksheet change event.
Because it was simpler than deleting them, to make them "invisible", I set the font size of the "encapsulating symbols" to one (1), the minimum. You could also make them the same color as the background, although this might result in inappropriate spacing. But be aware that merely changing format characteristics of a cell will not trigger the worksheet change event, so you may get funny results.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

=====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sEncapsule(0 To 1) As String
Dim StartSS As Long, LenSS As Long
Dim re As Object, mc As Object, m As Object
Dim i As Long

Application.EnableEvents = False

sEncapsule(0) = "[": sEncapsule(1) = "]"
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[" & sEncapsule(0) & "][^" & sEncapsule(1) & "]+[" & sEncapsule(1) & "]"

If Not Target.HasFormula Then
Set mc = re.Execute(Target.Text)
For Each m In mc
Target.Characters(m.firstindex + 1, m.Length).Font.Superscript = True
Target.Characters(m.firstindex + 1, 1).Font.Size = 1
Target.Characters(m.firstindex + m.Length, 1).Font.Size = 1
Next m
End If
Application.EnableEvents = True

End Sub
==================================