VBA处理SAP导出TXT/CSV文件的千分位与小数分隔符问题

导入SAP导出的TXT或CSV文件时，整数列处理正常，但含小数的数值格式异常。葡萄牙区域设置为空格作为千分位分隔符、逗号作为小数分隔符，库存值范围0,001至99 999,999，需统一格式，但多种尝试（含Stack Overflow方案）均失败。尝试禁用区域设置指定分隔符无效，SAP导出字段固定10字符：替换空格后Excel会篡改数值；保留空格作为字符串处理时，Excel会将点和逗号均识别为点。手动查找替换正常，但录制宏执行后结果错误。

原始值与期望导入值示例 #

Raw Value      Pretended Value
3.655,600      3655,6    (should remove the thousands separator)
10.548         10548     (should remove the thousands separator)
872            872       (once there is no separators, it should do nothing)
1.872          1872      (should remove the thousands separator)
16.000         16000     (should remove the thousands separator)
105,372        105,372   (only decimals separator, it should do nothing)
460,8          460,8     (only decimals separator, it should do nothing)
60,72          60,72     (only decimals separator, it should do nothing)
1.574,400      1574,400  (should remove the thousands separator)

当前使用的VBA代码 #

Columns("M:M").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Columns("M:M").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

执行代码后的错误结果 #

3.655,600     3 655 600 (it should be 3655,600)
10.548            10548 (correct)
872                 872 (correct)
1.872              1872 (correct)
16.000            16000 (correct)
105,372          105372 (it should maintain 105,372)
460,8              4608 (it should maintain 460,8)
60,72              6072 (it should maintain 60,72)
1.574,400     1 574 400 (it should be 1574,400)

解决方案 #

方案1：后期处理已导入的列 #

核心思路是先锁定文本格式避免自动转换，再替换千分位分隔符，最后转换为符合区域设置的数值格式：

Sub FixSAPNumbers()
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set rng = ws.Columns("M:M")
    
    '1. 设置为文本格式，防止Excel自动篡改分隔符
    rng.NumberFormat = "@"
    
    '2. 替换所有千分位的点为空，保留逗号作为小数分隔符
    rng.Replace What:=".", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
    
    '3. 转换为数值格式，适配葡萄牙区域的逗号小数分隔符
    rng.NumberFormat = "#,##0.###" '可根据需求调整小数位数
    rng.Value = rng.Value '触发文本转数值的转换
End Sub

方案2：导入时直接指定分隔符（推荐） #

从根源解决问题，导入阶段就正确识别SAP的数值格式（点为千分位、逗号为小数位）：

Sub ImportSAPFile()
    Dim filePath As String
    
    filePath = "C:\YourSAPExportFile.csv" '替换为你的文件实际路径
    
    Workbooks.OpenText Filename:=filePath, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, Comma:=True, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        ThousandsSeparator:=".", DecimalSeparator:=",", _
        FieldInfo:=Array(Array(13, xlGeneralFormat)) '第13列对应M列，设置为通用格式
End Sub

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