Subscript and Superscript with the TEXT function

[SteveH]

Greetings, All!

Is there code I can use with the TEXT function to apply Subscript or
Superscript formatting?

TIA

Steve H

 

[Dave Peterson]

Nope.

In fact, any cell that contains a formula can't use that character by character
formatting.

 

[Gord Dibben]

Steve

No, not with the TEXT Function.

You would have to use some VBA code to achieve this.

What are your needs. Perhaps there is another way to do what you want.


Gord Dibben Excel MVP

 

[SteveH]

Gord,

I know I can use

With Selection.Font
.Subscript = True
in VBA

But what I wanted to do was reference the value of i in another cell so the
cell of interest would read Xi where i would be in subscript and then copy
the formula to other locations. I was hoping there was an undocumented
parameter for the function TEXT that would allow this. If you have any
suggestions I would be glad to them

Thanks

Steve

 

[Harlan Grove]

SteveH wrote...

I know I can use

With Selection.Font
.Subscript = True
in VBA

A *portion* of text within the contents of a text constant stored in a
cell can never be Selection. Using Selection in this way, you change
the entire cell's display text to subscript.

But what I wanted to do was reference the value of i in another cell so the
cell of interest would read Xi where i would be in subscript and then copy
the formula to other locations. I was hoping there was an undocumented
parameter for the function TEXT that would allow this. If you have any
suggestions I would be glad to them

The worksheet TEXT function can *ONLY* change number formatting. It
can't change any font properties. There are no hidden codes.

What you want to do can be done with the .Characters collection of the
cell's text. Somehting like


Sub foo()
Dim x As Range, y As Range, z As Range

'modify next 3 statements as needed
Set x = Range("A1")
Set y = Range("B1")
Set z = Range("C1")

z.Characters.Text = x.Text & y.Text
z.Characters(Len(x.Text) + 1, Len(y.Text)).Font.Subscript = True
End Sub

 

[Gord Dibben]

Steve

The closest I can come is this macro for selected cells.

I assume the X is any string length, but I don't know how to get that string
length for each cell to set the value at Start:=2 as a variable.

Perhaps one of the real experts will jump in and modify for us.

Sub Add_Text_SubScript()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo justformulas
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = Range("B1").Value
For Each Cell In thisrng
Cell.Value = Cell.Value & moretext
With Cell.Characters(Start:=2).Font
.Subscript = True
End With
Next
Exit Sub
justformulas:
MsgBox "only formulas in range"
End Sub


Gord