在Excel VBA中将存储为文本的数字转换为数字的最简单方法[重复]

This question already has an answer here:

这个问题在这里已有答案：

VBA: Convert Text to Number 5 answers
VBA：将文本转换为5号答案

I trying to convert this text into numbers:

我试图将此文本转换为数字：

I have tried different formulas but Excel is not recognizing the format. However, using the code below it converts only the first three numbers and removes the rest.

我尝试了不同的公式，但Excel没有识别格式。但是，使用下面的代码只转换前三个数字并删除其余数字。

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = Val(r.Value)
    Next r
Next WS
End Sub

The result looks like this

结果看起来像这样

Does anyone have an ease fix for this without removing any numbers?

有没有人可以轻松解决这个问题而不删除任何数字？

3 个解决方案

#1

Providing this answer in relation with OP statement:

提供与OP声明相关的答案：

I have tried different formulas but Excel is not recognizing the format.

我尝试了不同的公式，但Excel没有识别格式。

There is no need to use VBA for that operation.

没有必要使用VBA进行该操作。

Use this formula if your decimal character is a period [.]

如果您的小数字符是句点[。]，请使用此公式

=SUM( SUBSTITUTE( SUBSTITUTE( B2, " ", "" ), ",", "." ) )

Or this one if your decimal character is a colon [,]

如果您的十进制字符是冒号[，]

=SUM( SUBSTITUTE( B2, " ", "" ) )

#2

Easiest way to convert a number stored as text, is to multiply it by 1,
So try this If IsNumeric(r) Then r.Value = (r.Value)*1
Or you can copy paste with a multiplication by 1 and this works too! ;)

转换存储为文本的数字的最简单方法是将其乘以1，所以试试这个如果是IsNumeric（r）然后r.Value =（r.Value）* 1或者你可以复制粘贴乘以1这个工作太！ ;）

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value)*1
        ''Or test the values in the next column on the right
        'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
    Next r
Next WS
End Sub

#3

Provided your data is always using a "space" as a thousand separator and a "comma" as a decimal point, you can use the following(Modified from your original script).

如果您的数据始终使用“空格”作为千位分隔符而“逗号”作为小数点，则可以使用以下内容（从原始脚本中修改）。

Sub ConvertTextNumberToNumber()
    For Each WS In Sheets
        On Error Resume Next
        For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)

            'For testing purposes I like to see the cell which is selected
            r.Select
            'Setting the Cell Number Format
            'for more or less decimals just add/remove 0 after the . 
            r.NumberFormat = "0.00"
            'Assigning the Cell value to a String
            String1 = r.Value
            'Replacing the spaces with no space
            String1 = Replace(String1, " ", "")
            'Replacing the comma with a point
            String1 = Replace(String1, ",", ".")
            'Putting the new value into the cell
            r.Value = String1
        Next r
    Next WS
End Sub

#1