VBA & Macro Problems

How to Replace and Format Special Characters with Symbol Font in WPS Spreadsheets VBA

Adam Davis Nov 13, 2026 869 views

Question details

The user needs to automate replacing and formatting special characters—such as degree, multiplication, mu, and alpha—inside Excel cells with the Symbol font by using VBA.

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user is working with cells that contain mixed text and special symbols and wants the target symbols to appear in the Symbol font without changing the rest of the cell content.
Observed behavior: Standard functions such as SUBSTITUTE and conditional formatting cannot apply different fonts to individual characters within a cell, so a VBA routine is required.

Before you start

Save the workbook as a macro-enabled file (.xlsm) and confirm the Developer tab is visible in WPS Spreadsheets. Open the VBA editor and insert the code in a standard module before running it on a copy of your data.

Solution 1Recommended

Use a VBA macro to loop through the used range and apply Symbol font to target characters

Recommended when multiple cells contain degree, multiplication, mu, or alpha symbols and each target character must be formatted individually within its cell text.

VBA can format individual characters by using the Characters(start, length) collection and Font.Name, which SUBSTITUTE and conditional formatting cannot do. The macro uses InStr to locate target characters, optionally replaces them, and then applies the Symbol font to only the matched character positions.

Open the VBA editor and insert a module

In WPS Spreadsheets, go to the Developer tab and click Visual Basic. In the editor, right-click the current workbook in Project Explorer, choose Insert > Module, and paste the macro code into the new module.

Define the character mappings and the target range

Create arrays or variables for the source characters, for example degree, multiplication, mu, and alpha, and the replacement characters. Set the target range to ActiveSheet.UsedRange or a specific range such as Range("A1:C100").

Loop through cells and locate each character with InStr

For each cell in the target range, store the cell text in a string variable. For each target character, use InStr(1, cellText, targetChar, vbTextCompare) to find its position, then store the position and evaluate whether replacement is needed.

Replace the character when required

If a replacement character is defined and the found character should be mapped, use the Mid statement or rebuild the cell string so that the source character is replaced by the desired character while preserving the rest of the text.

Apply Font.Name = "Symbol" to the matched substring

Use cell.Characters(startPosition, length).Font.Name = "Symbol" to format only the replaced or located character. Repeat the InStr search from the next position until no more target characters are found in that cell.

Verify character mappings: Run the macro on a copy of the worksheet first, because Symbol font character positions can differ from the original character codes and may need mapping adjustments.

Solution 2

Format one special character manually when the cell range is small

Use this for a one-off correction or a small number of cells where running a full macro is not necessary.

WPS Spreadsheets VBA Macro

Automate Symbol Font Formatting with a WPS Spreadsheets VBA Macro

Run a VBA macro in WPS Spreadsheets to locate degree, multiplication, mu, and alpha characters and apply the Symbol font to only those individual characters. This preserves the remaining text and gives you the same automated workflow as Excel.

1. Save your workbook as macro-enabled: In WPS Spreadsheets, choose File > Save As and select .xlsm format so VBA macros are retained.
2. Open the VBA editor: Go to Developer > Visual Basic, insert a standard module, and paste the character replacement and font formatting macro.
3. Run the macro on a test copy: Press F5 or click Run, then check that each degree, multiplication, mu, and alpha character is rendered in Symbol font and no other text is affected.
4. Apply to the used range: Set the macro target to ActiveSheet.UsedRange or a specific range and run it on the production worksheet after verification.

Formats individual characters that SUBSTITUTE and conditional formatting cannot handleKeeps the rest of the cell text unchanged while changing only the target symbolSupports macro-enabled workbooks for Microsoft Excel .xlsm compatibilityUses the familiar VBA editor and Characters object model in WPS SpreadsheetsFree and lightweight Office alternative for VBA-enabled spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Does WPS Spreadsheets support VBA macros for character formatting?

Yes, WPS Spreadsheets includes a VBA editor and supports the Characters object, so you can format individual characters within a cell by using Font.Name = "Symbol".

Why can’t SUBSTITUTE or conditional formatting replace and format symbols correctly?

SUBSTITUTE replaces text but cannot apply a different font to only part of a cell, and conditional formatting formats entire cells rather than individual characters. VBA can target a substring with Characters(start, length).

How do I open the VBA editor in WPS Spreadsheets?

Go to the Developer tab and click Visual Basic. If the Developer tab is not visible, enable it from the ribbon customization settings, then insert a standard module under the current workbook.

Will the Symbol font map degree, mu, alpha, and multiplication signs correctly on every machine?

Not always. Symbol font character positions are platform-dependent, so verify the exact character mapping on a copy of the workbook before running the macro on production data.

Can I limit the macro to a specific range instead of the whole sheet?

Yes, set the target range explicitly in the macro, for example Set rng = Range("A1:C100"), instead of using ActiveSheet.UsedRange.

Excel & Data Problems