format & function

[Guest]

How to restore superscript or subscript format of text while using functions
like concatenate etc

 

[Dave Peterson]

Formulas don't support this kind of character by character formatting.

 

[JE McGimpsey]

You can't - functions return values, not formatting.

You'd have to use an event macro instead. For instance, say your formula
was

F3: =A1 & " " & J1

Then you could put something like this in your worksheet code module
(right-click the worksheet tab and choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell1 As Range
Dim rCell2 As Range
Dim i As Long
Dim j As Long
If Not Intersect(Target, Range("A1,J1")) Is Nothing Then
Set rCell1 = Range("A1")
Set rCell2 = Range("J1")
With Range("F3")
.Clear
.Value = rCell1.Text & " " & rCell2.Text
For i = 1 To Len(rCell1.Text)
With .Characters(i, 1).Font
.Superscript = rCell1.Characters(i, 1).Font.Superscript
.Subscript = rCell1.Characters(i, 1).Font.Subscript
End With
Next i
For j = 1 To Len(rCell2.Text)
With .Characters(j + i, 1).Font
.Superscript = rCell2.Characters(j, 1).Font.Superscript
.Subscript = rCell2.Characters(j, 1).Font.Subscript
End With
Next j
End With
End If
End Sub

 

[Guest]

Thank you Mr. McGimpsey
Can this be extended say i would like to use this macro to concatenate
column A and column J in column F
with many thanks
Sridhar