How to Concatenate Text with Superscript Numbers in Excel

Formatting footnotes or mathematical notation in Excel can be incredibly frustrating, particularly when combining text with superscript numbers. While standard funding sources and academic institutions often require these precise citations in financial models, Excel's native concatenation functions strip away traditional rich text formatting.

Fortunately, leveraging specific Unicode character codes grants users the ability to seamlessly integrate superscript styling directly within dynamic formulas. A key stipulation, however, is that your chosen worksheet font must support these unique Unicode characters to display them correctly.

For instance, referencing a footnote with superscript 1 requires a formula like: ="Revenue"&CHAR(185).

Below, we will explore the exact character codes and formula structures needed to implement this clean styling across your worksheets.

Excel is an incredibly powerful tool for data analysis and reporting, but formatting text within formulas can sometimes feel like an uphill battle. A classic example of this is attempting to concatenate standard text with a superscript number (such as writing "m²" for square meters, "x³" for algebraic terms, or adding footnote references like "Revenue¹" to a table header).

If you have ever tried to combine text using a standard Excel formula-like =A1 & B1-only to find that Excel strips away all your carefully applied superscript formatting, you are not alone. Excel's calculation engine evaluates formulas as raw data, meaning character-level formatting (like superscripts or subscripts) is lost during standard concatenation.

Fortunately, there are several highly effective workarounds to solve this problem. In this comprehensive guide, we will explore the three best methods to concatenate text with superscript numbers in Excel: using Unicode formulas, utilizing VBA macros for dynamic formatting, and executing the quick Value-Paste-and-Format trick.

Method 1: The UNICHAR Formula Method (100% Dynamic)

The easiest, cleanest, and most robust way to concatenate text with superscript numbers without breaking your live formulas is by using the UNICHAR function.

Unicode is a universal character encoding standard that assigns a unique number to virtually every character, symbol, and script used across digital platforms. This includes dedicated, standalone Unicode characters for superscript numbers. Because these are distinct characters rather than formatted text, Excel formulas treat them as standard text values and preserve their appearance.

The Superscript Unicode Reference Table

To use this method, you need the specific decimal codes for the superscript characters. Refer to this quick-lookup table:

Example 1: Basic Concatenation for Measurement Units

Let's say you have the text "Area in m" in cell A2, and you want to append a superscript "2" to make it "Area in m²". You can use the ampersand (&) operator to concatenate the text with the UNICHAR function:

=A2 & UNICHAR(178)

Example 2: Dynamic Footnote References

If you want to append a superscript "1" as a footnote to a dynamic label in cell B2 (e.g., "Total Profit"), you can use:

=B2 & " " & UNICHAR(185)

This outputs: Total Profit ¹

Advanced: Handling Multi-Digit Superscripts in Excel 365

If you need to dynamically convert a multi-digit number (like "12" or "250") in cell C2 into superscript, you can write a nested formula using Excel 365's modern REDUCE and LAMBDA functions. This formula loops through each digit and replaces it with its corresponding superscript character:

=REDUCE(C2, {"0","1","2","3","4","5","6","7","8","9"}, LAMBDA(text,val, SUBSTITUTE(text, val, CHOOSE(val+1, UNICHAR(8304), UNICHAR(185), UNICHAR(178), UNICHAR(179), UNICHAR(8308), UNICHAR(8309), UNICHAR(8310), UNICHAR(8311), UNICHAR(8312), UNICHAR(8313)))))

If cell C2 contains 105, this formula returns ¹⁰⁵ dynamically, allowing you to combine it with any text string!

Method 2: Using VBA to Concatenate and Format (Perfect for Legacy Excel)

If you are working with an older version of Excel, or if you want to use true font formatting (which handles letters as well as numbers in superscript), a quick VBA macro is the most robust approach.

Because Excel formulas cannot apply rich text formatting (bolding, italicizing, or superscripting a part of a cell's string), we can use a macro to combine the values and explicitly format the superscript portion.

The VBA Code

To implement this, press ALT + F11 to open the VBA Editor, insert a new Module (Insert > Module), and paste the following code:

Sub ConcatenateWithSuperscript()
    Dim ws As Worksheet
    Dim rngText As Range
    Dim rngNum As Range
    Dim cell As Range
    Dim textLen As Integer
    Dim numLen As Integer
    
    Set ws = ActiveSheet
    
    ' Loop through rows 2 to 20 (adjust this range to fit your needs)
    For i = 2 To 20
        ' Read base text from Column A, number from Column B, output to Column C
        If ws.Cells(i, 1).Value <> "" Then
            Dim baseText As String
            Dim superText As String
            
            baseText = ws.Cells(i, 1).Value
            superText = CStr(ws.Cells(i, 2).Value)
            
            ' Place combined unformatted text in Column C
            ws.Cells(i, 3).Value = baseText & superText
            
            ' Calculate character positions
            textLen = Len(baseText)
            numLen = Len(superText)
            
            ' Format only the concatenated number portion as superscript
            ws.Cells(i, 3).Characters(Start:=textLen + 1, Length:=numLen).Font.Superscript = True
        End If
    Next i
End Sub

How to Run It:

1. Place your base text in Column A (e.g., "Footnote") and your target superscript numbers in Column B (e.g., "3").
2. Press ALT + F8, select ConcatenateWithSuperscript, and click Run.
3. Column C will fill with perfectly concatenated values (e.g., "Footnote³") using true rich text formatting.

Method 3: The Copy, Paste-Value, and Manual Format Trick (No-Code Workaround)

If you only need to perform this task once and do not want to set up complicated formulas or VBA scripts, you can use the traditional paste-special workaround.

1. Write your standard concatenation formula. For example, in cell C2, enter: =A2 & B2.
2. Select cell C2, copy it (CTRL + C), right-click the destination cell, and select Paste as Values. This converts the formula into plain text.
3. Double-click the cell (or press F2) to enter Edit Mode.
4. Using your mouse, highlight only the portion of the text that you want to transform into a superscript.
5. Right-click the highlighted text and choose Format Cells... (or press CTRL + 1).
6. Under the Effects section, check the box next to Superscript, and click OK.
7. Press Enter to commit the change. Your selected characters will now be displayed as superscript numbers.

Summary: Choosing the Right Method

To determine the best approach for your specific Excel worksheet, consult this summary breakdown:

• Use the UNICHAR Method if you need your spreadsheet to remain completely automated, dynamic, and formula-driven without using macros. It is highly compatible with modern versions of Excel and Excel Online.
• Use the VBA Method if you are compiling large databases, need to process complex superscript strings (including letters, symbols, or long strings), or are working with legacy Excel versions.
• Use the Copy-Paste Method if you are preparing a one-off presentation slide, static report, or printout where data will not change.

By leveraging these techniques, you can overcome Excel's formatting limitations and create professional, scientifically accurate, and beautifully styled spreadsheets.