superscript

[leon22]

Hi,

I am trying to design a formula that concatenates in cell A3 th
content of cells A1 and A2 together, and by displaying the content o
cell A2 in superscript. This has to be done through a formula, as
want it to be dynamic, and be able to copy elsewhere in the worksheet.

A1 = CAR
A2 = (blue)

A3 should return CAR(blue), but the "(blue)" must be superscript.

P.S. I know this could be done by creating a custom function (usin
VB), but I would prefer doing it using standard Excel functions

 

[JE McGimpsey]

It can't be done with either built-in or custom functions. Character
formatting can only be done on constants.

To be dynamic you can use an event macro. Assume you want to use the
event for A1:A2 in A3 and J3:J4 in J5. Put something like this in the
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
Set rng = Range("A1:A3")
ElseIf Not Intersect(Target, Range("J3:J4")) Is Nothing Then
Set rng = Range("J3:J5")
End If
If Not rng Is Nothing Then
With rng
SuperSecond .Item(3), .Item(1).Text, .Item(2).Text
End With
End If
End Sub

Put this in a regular code module:

Public Sub SuperSecond(rng As Range, _
sText1 As String, sText2 As String)
Application.EnableEvents = False
With rng
.Clear
.Value = sText1 & sText2
.Characters(Len(sText1) + 1).Font.Superscript = True
End With
Application.EnableEvents = True
End Sub

If you're not familiar with macros, see David McRitchie's "Getting
Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[leon22]

Hi JE,

The more I think about it, I am sure you are right; it cannot be don
by built-in or custom functions.

Now, thanks a lot for your answer. I will need a couple of days t
digest your code... It is quite complex for me, but taking one line a
a time, I will succeed. I already understand what you are trying t
achieve...

It is definitely very much appreciated.

Leo