Excel VBA实现美式数字格式转欧式格式的问题

Let me break down why your Replace method is spitting out those massive, incorrect values first:

When you run a straight Replace(myCell.Value, ".", ","), Excel interprets the comma as a thousands separator (per default regional settings) instead of a decimal separator. So your original 1.536962890625000000e+03 gets turned into 1,536962890625000000e+03—which Excel reads as 1 million-plus multiplied by 10³, hence the absurdly large number.

Here are two reliable fixes tailored to different data scenarios:

Option 1: If your data is stored as text (most common for imported datasets) #

This converts text-based American scientific notation to a proper Excel value, then formats it to use European-style decimal separators:

Sub ConvertScientificTextToEuropean()
    Dim targetRange As Range
    Dim cell As Range
    
    ' Set your target column/range here (e.g., Column A with text values)
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
    
    ' Temporarily override Excel's separators to match European format
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = ","
    Application.ThousandsSeparator = "." ' Optional, if you need thousand separators too
    
    For Each cell In targetRange
        ' Convert text to a numerical value (CDbl recognizes American decimal separators)
        cell.Value = CDbl(cell.Value)
        ' Apply scientific notation format with European decimal separator
        cell.NumberFormat = "0,000000000000000e+00"
    Next cell
    
    ' Restore system default separators to avoid breaking other workbooks
    Application.UseSystemSeparators = True
End Sub

Option 2: If your data is already a numerical value #

No need for messy replace operations—just reformat the cells directly to use European scientific notation:

Sub FormatExistingNumbersToEuropean()
    Dim targetRange As Range
    
    ' Target your column/range with numerical values
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
    
    ' Temporarily switch to European separators
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = ","
    ' Apply the desired scientific notation format
    targetRange.NumberFormat = "0,000000000000000e+00"
    ' Reset to system defaults
    Application.UseSystemSeparators = True
End Sub

Quick Tips for Large Datasets: #

• Using SpecialCells targets only relevant cells (text or numbers) instead of looping every cell in the column—this cuts down runtime drastically for big data.
• Adjust the NumberFormat string to match your required precision (the 0,000... part controls how many decimal places are displayed).
• Always restore Application.UseSystemSeparators = True after running the macro to avoid messing up other open workbooks.

内容的提问来源于stack exchange，提问作者TheLegend27