Concatenate cells containing exponents

[CJ]

Using Excel 2003, is it possible to concatenate cells containing exponents
without getting the carat? I don't want to concatenate 2 and the exponent 5
and get the result 2^5 when the 5 is already a superscript. The 5 needs to
remain as a superscript. Thanks.

 

[Dave Peterson]

Formulas don't allow this type of character by character formatting.

 

[CJ]

From my original post: is it possible to use VBA to write the base with the
exponent so that there is no carat? How would I do this? I want to display an
equation but the exponent will change. Any ideas please. Thank you.

 

[Rick Rothstein]

I think we need some more information. You say superscript and caret when
referring to the value in a cell... exactly what value is in the cells and
how are you concatenating them?

 

[Rick Rothstein]

Just as a clarification on my last post... what I am asking for is an
example for two cells showing the values in them and the concatenated value
you want to produce from them. I'm specifically interested in seeing an
example of when the exponents are different from each other (if that is a
case that can in fact exist in your worksheet).

 

[Dave Peterson]

If the value is text--not a real number, you can record a macro while you
superscript the characters you want.

If your values are digits, then you'll have to preformat the cell as text first
or start the entry with a leading apostrophe.

You could do the data entry using the caret (2^3) which will treat the entry as
text and then use an event macro that applies the superscript to everything
after the caret.

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the newly opened code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToWatch As Range
Dim myIntersect As Range
Dim myCell As Range
Dim CaretPos As Long

Set RngToWatch = Me.Range("a:a")

Set myIntersect = Intersect(Target, RngToWatch)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
CaretPos = InStr(1, .Value, "^", vbTextCompare)
If CaretPos = 0 Then
'do nothing
Else
Application.EnableEvents = False
.NumberFormat = "@"
.Value = Replace(.Value, "^", "")
With .Characters(Start:=CaretPos, _
Length:=Len(Target.Value)).Font
.Superscript = True
End With
Application.EnableEvents = True
End If
End With
Next myCell

End Sub

I checked column A. You may want to use a specific address ("C3:d99")???

If you ever have to superscript/subscript lots of different characters, you may
want to try John Walkenbach's addin:

http://spreadsheetpage.com/index.php/file/superscript_subscript_formatting_add_in/

Or even get John's Pup Utilities:
http://spreadsheetpage.com/index.php/pupv6/utilities/