VBA & Macro Problems

How to Fix Excel VBA Find and Replace Macro Leaving Numbers as Text

Adam Davis Oct 10, 2026 869 views

Question details

The user needs to correct a VBA macro that executes a Find and Replace operation on CSV data but fails to convert the modified text strings into actual numeric values.

How to Fix an Excel VBA Find and Replace Macro That Leaves Numbers as Text

Product: Excel
Device & OS: not provided
Scenario: Formatting and cleaning up figures copied from a CSV file using an automated VBA macro.
Observed behavior: The recorded macro successfully replaces the decimal separators, but the resulting values remain stored as text instead of being recognized as standard numbers.

Before you start

Verify your system's regional settings to confirm whether your default decimal separator is a comma or a period, as this dictates how the VBA macro evaluates numeric data after the replacement.

Solution 1Recommended

Modify the VBA Macro to Update Number Format

Adjust your VBA script to format the target range as 'General' before executing the replacement, ensuring Excel recognizes the new values as numbers.

When recording a simple Find and Replace action, Excel often retains the original cell formatting. By programmatically changing the NumberFormat to 'General' right before the replacement, you force Excel's calculation engine to re-evaluate the cell contents.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic Editor and locate the module containing your recorded macro.

Add the NumberFormat Command

Before the replacement line, insert the following code: Selection.NumberFormat = "General".

Add the Replacement Command

Ensure your replacement line correctly identifies the decimal separators, such as: Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart.

Select Range and Run

Return to your worksheet, highlight the exact range of CSV data you want to modify, and run the macro.

Macro Execution Check: If the macro fails to change the format on the first try, verify that you have explicitly selected the range before running it. Running it a second time on the active selection often forces the update.

Solution 2

Force Numeric Conversion via Paste Special

If your macro successfully replaces the separators but still leaves the cells formatted as text, you can use the Paste Special Multiply method to force a numeric conversion.

Solution 3

Convert Text to Numbers Using Text to Columns

The Text to Columns wizard is a built-in feature that quickly re-evaluates text strings as numbers without needing complex formulas.

WPS Spreadsheet Macros

Seamlessly Handle CSV Data and Macros in WPS Spreadsheet

WPS Spreadsheet provides robust VBA support, making it incredibly easy to automate data cleaning tasks like converting CSV text to numeric values. You can run your existing Excel macros directly in WPS or use its smart conversion tools.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the main menu, and ensure your 'Developer Tools' are active to access macro functionalities.
2. Import Excel Macros: Open the Visual Basic Editor from the Developer tab and easily paste or modify your existing Excel VBA code.
3. Run on CSV Data: Highlight your imported CSV figures, open the Macros dialog, select your Find and Replace script, and click 'Run'.
4. Quick Error Resolution: If numbers import as text, click the small warning icon next to the selected cells and choose 'Convert to Number' for an immediate fix.

Fully compatible with Microsoft Excel VBA macros (.xlsm)Smart 'Convert to Number' feature fixes text format instantlyAdvanced Text to Columns wizard for effortless CSV importingFree to download and lightweight on system resources

Frequently Asked Questions

Why does Excel leave my numbers as text after Find and Replace?

Excel frequently retains the original data type of a cell. If the data was imported from a CSV file as text, a simple Find and Replace alters the characters but does not trigger the spreadsheet's calculation engine to re-evaluate the string as a number.

Can I automate the Text to Columns fix within my VBA macro?

Yes. You can add a VBA command that calls the Text to Columns method on your range. For example, adding 'Selection.TextToColumns DataType:=xlDelimited' after your replacement code will force Excel to evaluate the updated text as numeric values.

Why didn't my VBA format change apply on the first run?

This commonly happens if the macro assumes an active selection but a specific range wasn't highlighted before executing. Always ensure your script either explicitly defines the target range (e.g., Range("A1:A100")) or that you manually select the data before pressing Run.

Excel & Data Problems