Superscript in Formulas

[damianjmcgrath]

Hello,

In Cell A1, I have the number 1.085 (although this could be anything, as it gets calculated based on other cells).
In Cell A2, I have the number 27 (again, this can change).

In Cell A3, I want it to display A1 to the power of A2 (not the result of that sum, but the actual text). For example, I want it to display 1.085^27.

In Cell A4, I want the actual answer of 1.085^27, so it would display 9.049.


I'm happy with the formula for A4 - I can get Excel's POWER() function working properly, but Cell A3 is causing me issues. I can't seem to get the 27 appearing as a superscript text (i.e., small font and above the 1.085).


Can I have two different font types in 1 formula? I think that's what I need - is this possible?

Thanks.

 

[damianjmcgrath]

I did actually try setting the Format Cells option of cell A2 to superscript, so the 27 in that cell is appearing as a superscript font.

However, in cell A3, my formula of "=A1 & A2" is showing as 1.08527.


Is there a way to have the formula in A3 pull the data from the other two cells, but keeping those two cells' different format styles?

 

[damianjmcgrath]

I have also tried VBA code:

    Dim a, b, c As String
    a = Range("A1").Value
    Range("A2").Font.Superscript = True
    b = Range("A2").Value
    c = a & b
    Range("A3").Value = c

But this still returns 1.08527 in cell A3, instead of 1.085^27.

 

[Weaver]

You need to calculate the value for A3 in the macro then convert to text

try:

 Private Sub Worksheet_Calculate()
    ns = Len(Range("A1").Value)
    ss = Len(Range("A2").Value)
    With Range("A3")
        .NumberFormat = "@"
        .Value = Range("A1").Value & Range("A2").Value
        With .Characters(Start:=ns + 1, Length:=ss).Font
            .Superscript = True
        End With
    End With
End Sub

Right click the worksheet tab, choose 'view code' and paste the macro there.

HTH