VBA & Macro Problems

How to Convert Text-Formatted Numbers to Numbers with an Excel Macro

Muhammad Talha Oct 22, 2026 868 views

Question details

The user needs a VBA macro that can dynamically convert numbers stored as text into actual numeric values for any currently selected range, without requiring hardcoded cell references.

How to Convert Text-Formatted Numbers to Numbers with an Excel Macro

Product: Excel
Device & OS: not provided
Scenario: Cleaning up imported or pasted data where numbers are formatted as text, which prevents mathematical formulas and data analysis from working properly.
Observed behavior: Numbers remain stored as text until manually converted. A VBA solution is needed to automate the re-evaluation of the selected cells.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application to access the VBA editor. Be aware that this macro will replace any formulas in your selected range with their static calculated results.

Solution 1Recommended

Use a Simple Value Replacement Macro

This straightforward VBA macro forces the spreadsheet to re-evaluate the selected cells, automatically triggering the conversion of text-formatted numbers into real numbers.

Spreadsheet software automatically converts values that look like numbers into numeric data types when they are entered. By using VBA to replace a range's values with themselves, you simulate re-entering the data, which triggers this automatic conversion.

Open the VBA Editor

Press the ALT + F11 keys on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu, then select 'Module' to create a new blank module for your code.

Paste the Macro Code

Type or paste the following code into the module window: Sub ForceNumFormat() Selection.Value = Selection.Value End Sub

Run the Macro

Close the VBA editor. Highlight the cells on your worksheet that contain the text-formatted numbers, press ALT + F8, select 'ForceNumFormat', and click 'Run'.

Warning Regarding Formulas: This macro removes underlying formulas from the selected range and replaces them with their current static result values. Only select cells containing raw data.

Solution 2

Apply an Advanced Formatting Macro

Use this enhanced macro if you want to simultaneously convert the text to numbers, specify a number format, and align the text.

Seamless Data Processing

Quickly Convert Text to Numbers in WPS Spreadsheet

WPS Spreadsheet offers powerful data formatting tools and full VBA/Macro support, allowing you to clean up text-formatted numbers effortlessly while maintaining full compatibility with Microsoft Excel files.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open the document containing the text-formatted numbers.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'VBA Editor'.
3. Paste and Run the Macro: Insert a module, paste the conversion macro code, select your data range in the worksheet, and execute the macro to format your numbers instantly.

Full compatibility with Microsoft Excel VBA macros and .xlsm filesBuilt-in error checking smart tags for quick text-to-number conversion without codingLightweight and fast for handling large datasetsFree Office alternative with a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does setting a cell's value to itself convert text to numbers?

Spreadsheet applications like Excel automatically evaluate and convert values that resemble numbers, dates, or times. Overwriting a cell with its own value using VBA mimics the action of manually typing the data and pressing Enter, which forces the system to re-evaluate and apply the correct numeric format.

Will this macro delete the formulas in my selected cells?

Yes. The macro command 'Selection.Value = Selection.Value' reads the calculated result of a formula and writes it back as a static value. You should only use this macro on ranges containing raw data, not formula-driven cells.

How can I convert text-formatted numbers without using a VBA macro?

You can use the built-in Error Checking smart tag (the small warning icon next to the cell) and click 'Convert to Number'. Alternatively, you can use the 'Text to Columns' feature under the Data tab and simply click 'Finish', or use 'Paste Special' to multiply the entire range by 1.

Excel & Data Problems