Combine Excel Cells with Superscript Text Using VBA
Question details
The user needs to merge text from two different cells and apply superscript formatting to a specific portion of the combined string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining text fields where one part, such as a trademark symbol, mathematical power, or footnote reference, needs to be formatted as superscript in the final cell.
- Observed behavior
- Standard cell concatenation formulas (like CONCATENATE or the ampersand operator) strip all rich text formatting, leaving the result as plain text. The user must use VBA to programmatically reapply the superscript formatting after joining the text.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the Developer tab enabled on your ribbon to access the VBA Editor.
Use VBA Characters Property to Apply Superscript
This method uses a VBA macro to join the cell values together and then applies superscript formatting exclusively to the specific characters originating from the second cell.
Because regular Excel formulas cannot preserve text formatting when concatenating, VBA is required. The script works by merging the text into a destination cell and then calculating the length of the appended text to apply the '.Font.Superscript = True' property to just that segment.
Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.
In the top menu, click 'Insert' and select 'Module' to create a new blank script window.
Copy and paste the following code into the module window: Sub Test() With Range("C1") .Value = Range("A1") & Range("B1") .Characters(Len(.Value) - Len(Range("B1")) + 1).Font.Superscript = True End With End Sub
Close the VBA editor and press 'Alt + F8'. Select 'Test' from the Macro dialog box and click 'Run'. The combined text will appear in cell C1 with the B1 segment formatted as superscript.
Automate Formatting and Macros Easily in WPS Office
WPS Spreadsheet features a comprehensive, built-in Macro Editor that fully supports VBA scripting. You can seamlessly run scripts to concatenate cells, apply rich text formatting, and automate repetitive tasks just as you would in Microsoft Excel.
- 1. Download and Install: Get WPS Office from the official website and install it on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm file.
- 3. Access the Macro Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macro' to open the VBA editor.
- 4. Run Your Code: Paste your concatenation and superscript formatting VBA code and execute it instantly.
Frequently Asked Questions
Can I combine cells with superscript formatting using standard Excel formulas?
No, standard Excel formulas like CONCATENATE, TEXTJOIN, or using the ampersand (&) operator only extract the plain text values from cells. They do not carry over rich text formatting like bold, italic, or superscript. VBA is required to achieve this.
How do I apply subscript instead of superscript using VBA?
You can easily modify the provided VBA code to apply subscript. Change the property '.Font.Superscript = True' to '.Font.Subscript = True' before running the macro.
Why is my VBA code returning a 'Subscript out of range' error?
This error typically happens if the worksheet containing your target cells is not active, or if there is a typo in the Range references. Make sure you are on the correct active sheet or explicitly define the worksheet in the code, such as Sheets("Sheet1").Range("A1").
