VBA & Macro Problems

How to Convert Numbers in Text to Subscript Using VBA

Khadija Khan Oct 14, 2026 869 views

Question details

How to write a VBA function that automatically formats all numerical digits within a text string as subscripts.

How to Convert Numbers in Text to Subscript Using VBA

Product: Spreadsheet
Device & OS: not provided
Scenario: When formatting chemical formulas, math variables, or specific string layouts, users often need all numbers inside a text cell to be displayed as subscripts.
Observed behavior: Attempting to use a User-Defined Function (UDF) to apply subscript formatting fails, as UDFs can only return standard string values and cannot apply rich-text character formatting to parts of a cell.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet application and remember to back up your worksheet, as running a VBA macro clears your Undo history.

Solution 1Recommended

Use a VBA Macro (Sub Procedure) to Apply Subscript Formatting

Because a standard VBA UDF cannot change font properties, you must use a macro that iterates through the characters in selected cells and applies subscript formatting directly.

A VBA User-Defined Function (UDF) cannot reliably format individual characters in a worksheet cell while returning a normal string value. UDFs are strictly designed to perform calculations and return values.

To display digits as subscripts, you must format the cell's character ranges using a VBA Sub procedure instead of using a UDF as the formatting mechanism.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu, then select 'Module' to create a blank script window.

Paste the Formatting Code

Copy and paste the following code into the module window: Sub ConvertNumbersToSubscript() Dim cell As Range Dim i As Long Dim ch As String For Each cell In Selection For i = 1 To Len(cell.Value2) ch = Mid$(CStr(cell.Value2), i, 1) If ch Like "#" Then cell.Characters(i, 1).Font.Subscript = True End If Next i Next cell End Sub

Run the Macro on Selected Cells

Close the VBA editor and return to your worksheet. Highlight the cells containing the text you want to format. Press 'Alt + F8', select 'ConvertNumbersToSubscript' from the list, and click 'Run'.

Use a VBA Macro (Sub Procedure) to Apply Subscript Formatting

Wildcard Matching: The line 'If ch Like "#" Then' specifically checks if the current character is a numeric digit (0-9). Only characters that match this rule will be converted to subscript.

Automate with WPS Office

Easily Run VBA Macros in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to automate repetitive formatting tasks like subscripting numbers with an intuitive and familiar interface.

1. Access Developer Tools: Open WPS Spreadsheet, navigate to the 'Tools' tab, and click on 'Developer' to reveal macro options.
2. Launch the VBA Editor: Click the 'VBA Editor' button to open the coding environment where you can manage all your modules.
3. Execute Your Macros: Insert a module, paste your subscript VBA code, and easily run it using the Macro dialog to format your data instantly.

High compatibility with Microsoft Excel VBA scripts and macrosSeamlessly opens and saves .xlsm and .xlsb spreadsheet formatsLightweight installation with fast loading and executing timesBuilt-in Developer tools for editing and debugging code easily

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my VBA User-Defined Function (UDF) change the font to subscript?

Spreadsheet UDFs are designed exclusively to return a value to the calling cell. They are not permitted to change the Excel environment or alter cell formatting, which includes applying rich-text character formats like subscript or bold. You must use a Sub procedure (macro) to apply formatting.

Can I format other characters, like letters, as subscript using this macro?

Yes. You can modify the 'If ch Like "#" Then' line in the VBA code to check for specific letters or patterns. For example, replacing "#" with "[A-Z]" would format uppercase letters as subscripts instead of numbers.

How do I undo a VBA macro that changed my text formatting?

Actions performed by a VBA macro usually clear the Undo stack entirely, meaning you cannot use Ctrl+Z to revert the changes. It is highly recommended to always save a copy or backup of your worksheet before running any formatting macro.

Excel & Data Problems