在 VBA 中使用 Replace 方法将数字存储为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10075968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using the Replace method in VBA leaves numbers stored as strings
提问by Cutter
I'm working with a workbook which uses a strange space as a thousands separator. The numbers including this separator are treated as text by Excel. When I remove this separator using the Find & Replace tool, the numbers get properly recognized as numbers. When I do the same using VBA, using the Cells.Replace method, the numbers keep being stored as text. A green corner then appears on the cells containing these numbers, allowing me to convert them to the number format, but this option doesn't seem to be exploitable in VBA as this action isn't recorded by the macro recorder.
我正在使用一个工作簿，它使用一个奇怪的空格作为千位分隔符。Excel 将包含此分隔符的数字视为文本。当我使用查找和替换工具删除此分隔符时，数字会被正确识别为数字。当我使用 VBA 做同样的事情时，使用 Cells.Replace 方法，数字继续存储为文本。然后在包含这些数字的单元格上会出现一个绿色角，允许我将它们转换为数字格式，但此选项似乎无法在 VBA 中利用，因为宏记录器未记录此操作。
I may copy my worksheet's cells to an array, then loop through the values to replace the thousands separator, then paste the array back to the worksheet. My question is: is there a more efficient way to remove these thousands separators so that my numbers get treated as numbers?
我可以将我的工作表的单元格复制到一个数组，然后遍历这些值以替换千位分隔符，然后将数组粘贴回工作表。我的问题是：有没有更有效的方法来删除这些千位分隔符，以便我的数字被视为数字？
采纳答案by Cutter
Numbers with a non-break space as thousands separator and a coma as decimal separator were stored as text. Using the Cells.Replacemethod in VBA to remove the space didn't convert the values to number format, instead leaving them stored as text. The solution was using Cells.Replacea second time to replace comas with dots for VBA to treat the values as numbers and copy them to the worksheet as such. Excel then formats the numbers according to the regional settings (replacing dots back with comas if necessary).
以连续空格作为千位分隔符和逗号作为小数分隔符的数字被存储为文本。使用Cells.ReplaceVBA 中的方法删除空格不会将值转换为数字格式，而是将它们存储为文本。解决方案是使用Cells.Replace第二次用 VBA 的点替换逗号，以将值视为数字并将它们复制到工作表中。Excel 然后根据区域设置格式化数字（如有必要，用逗号替换点）。
回答by Siddharth Rout
Do you mean the space between 9 and 5 in Cell B3 and B5? If yes, then just removing the space will not solve the problem. You need to remove the comma as well to convert it into a number and then format it using a proper thousand separator. See this code and the snapshots
你是说 B3 和 B5 单元格中 9 和 5 之间的空格吗？如果是，那么仅仅删除空格并不能解决问题。您还需要删除逗号以将其转换为数字，然后使用适当的千位分隔符对其进行格式化。查看此代码和快照
SNAPSHOT
快照
CODE
代码
Sub Sample()
With Sheets("Feuil1").Columns(2)
.Replace What:="?", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.NumberFormat = "#,##0"
End With
End Sub
HTH
HTH
回答by user3445773
The above answer is right. The issue comes from Excel regional settings. (i.e. : In France numbers are formatted 1234,56 whereas it would be 1234.56 in the english version of Excel) Using the find & replace tool, does not affect the regional settings.
上面的答案是对的。该问题来自 Excel 区域设置。（即：在法国，数字的格式为 1234,56，而在英文版 Excel 中则为 1234.56） 使用查找和替换工具不会影响区域设置。
However when using VBA to replace dots by comas, VBA being english based sets all the numbers as string. If you replace separators back to dots, numbers will show up following the regional settings (dots for english settings, comas for France).
但是，当使用 VBA 用逗号替换点时，基于英语的 VBA 将所有数字设置为字符串。如果您将分隔符替换回点，数字将按照区域设置显示（英语设置为点，法国设置为逗号）。
Hope this helps
希望这可以帮助
