在VBA中使用Replace方法将数字存储为字符串 [英] Using the Replace method in VBA leaves numbers stored as strings

问题描述

我正在使用一个使用奇怪空间作为千位分隔符的工作簿。包含此分隔符的数字将被Excel处理为文本。当我使用Find& amp;替换工具，数字被正确识别为数字。当我使用VBA进行相同操作时，使用Cells.Replace方法，数字将保存为文本。然后，包含这些数字的单元格上会出现一个绿色的角落，允许我将它们转换成数字格式，但是这个选项在VBA中似乎不会被利用，因为这个操作不会被宏记录器记录下来。

我可以将我的工作表的单元格复制到数组，然后循环遍历值来替换数千分隔符，然后将该数组粘贴到工作表。我的问题是：有没有更有效的方法来删除这几千个分隔符，以便我的数字被视为数字？

解决方案

数字具有不间断空格为千位分隔符和以小数分隔符为单位的逗号作为文本存储。在VBA中使用 Cells.Replace 方法来删除空间并没有将值转换为数字格式，而是将它们保存为文本。该解决方案第二次使用 Cells.Replace 来替换vBA的点以将值视为数字，并将它们复制到工作表中。 Excel然后根据区域设置格式化数字（如有必要，用comas替换点）。

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.

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?

解决方案

Numbers with a non-break space as thousands separator and a coma as decimal separator were stored as text. Using the Cells.Replace method 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.Replace a 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).

这篇关于在VBA中使用Replace方法将数字存储为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！