我可以使用 VBA 来检测数字是否以文本形式存储在单元格中吗？

Can I use VBA to detect if a number is stored as text in a cell?

提问by user1283776

I am thinking somehting along the lines of:

我在想一些事情：

if Range("A1").NumberFormat = "@" AND test to show that the content of A1 can be a number Then
    Debug.Print("A1 is a number stored as text")
end if

采纳答案by Siddharth Rout

Can I use VBA to detect if a number is stored as text in a cell?

我可以使用 VBA 来检测数字是否以文本形式存储在单元格中吗？

Let me play too :)

让我也玩：）

To do an exact test to check if a number is stored as text in a cell, use this

要进行精确测试以检查数字是否以文本形式存储在单元格中，请使用此

Sub Sample()
    If (Range("A1").NumberFormat = "@" And IsNumeric(Range("A1"))) Or _
    (Range("A1").PrefixCharacter = "'" And IsNumeric(Range("A1"))) Then
       Debug.Print ("A1 is a number stored as text")
    End If
End Sub

This will take care of scenarios like this

这将处理这样的场景

Note that in the first cell the numberformat is Generaland the number is stored as Text using '

请注意，在第一个单元格中，数字格式是General，并且数字使用文本存储为'

EDIT:

编辑：

To demonstrate this a little further. We will convert the above sub to a UDF

为了进一步证明这一点。我们将上述 sub 转换为 UDF

Function IsNumberStoredAsText(Rng As Range)
    IsNumberStoredAsText = "Number is not stored as text"

    If (Rng.NumberFormat = "@" And IsNumeric(Rng)) Or _
    (Rng.PrefixCharacter = "'" And IsNumeric(Rng)) Then
       IsNumberStoredAsText = "Number stored as text"
    End If
End Function

Now using it in the worksheet

现在在工作表中使用它

回答by Dmitry Pavliv

UPD:

更新：

All solutions are good, but doen't work in next cases:

所有解决方案都很好，但在以下情况下不起作用：

It's also possible to have cells with Number format(or any other format) but with numbers stores as text (you can see it in my test wotkbook). Imagine the situation when you have number in cell with Textformat and then you change Textformat to the Nubmer format(in cells properties or using "Format" combobox in the Ribbon). Your value would be still stored as text (despite the fact that excel says that it stores in Number Format) untill you press F2and ENTER(you can check it in my test workbook). You can simply reproduce this behavior:change format of cell to Text, then enter value (i.e. 123), and then change format of cell to Number Format(exactly in this order). Now your value stored as text but has Number Format.

单元格也可以Number format（或任何其他格式）但数字存储为文本（您可以在我的测试 wotkbook 中看到它）。想象一下当您在单元格中有Text格式的数字然后将Text格式更改为Nubmer format（在单元格属性中或使用功能区中的“格式”组合框）时的情况。您的值仍将存储为文本（尽管 excel 说它存储在 中Number Format），直到您按下F2和ENTER（您可以在我的测试工作簿中检查它）。您可以简单地重现此行为：将单元格的格式更改为Text，然后输入值（即 123），然后将单元格的格式更改为Number Format（完全按照此顺序）。现在您的值存储为文本，但具有Number Format.

So, examine Range.NumberFormatis a wrong way.

所以，检查Range.NumberFormat是错误的方法。

Here is Test workbook

这是测试工作簿

There is a very simple and elegant way to detect if a number stored as text or as number:

有一种非常简单而优雅的方法来检测数字是存储为文本还是数字：

Function IsNumberStoredAsText(Rng As Range)
    If Not IsNumeric(Rng) Then
        IsNumberStoredAsText = "NOT number"
        Exit Function
    End If

    IsNumberStoredAsText = "Number NOT stored as text"

    If Rng.Value + "0" <> Rng.Value Then
       IsNumberStoredAsText = "Number stored as text"
    End If
End Function

How it works:

这个怎么运作：

Let cell A1contains number "123" stored as text. In this case Range("A1").Value + "0"would concatenate two strings and return "1230", but if A1contains number 123 stored as number, "0"would be casted to 0and Range("A1").Value + "0"would return number 123

让单元格A1包含存储为 text 的数字“123” 。在这种情况下Range("A1").Value + "0"将连接两个字符串并返回“1230”，但如果A1包含存储为 number 的数字 123 ， "0"则将被强制转换为0并Range("A1").Value + "0"返回数字 123

Result:

结果：

回答by Peff

There is actually a more convenient way to detect whether or not a cell contains a number stored as text :

实际上有一种更方便的方法来检测单元格是否包含存储为文本的数字：

    If Range("A1").Errors.Item(xlNumberAsText).Value = True Then
       MsgBox "Number Stored As Text"
    Else
       MsgBox "Number Not Stored As Text"
    End If

A.

一种。

回答by chris neilsen

The test you seek is

你寻求的测试是

ISNUMERIC(...)

From Excel help:

从 Excel 帮助：

Returns a Boolean value indicating whether an expression can be evaluated as a number.

返回一个布尔值，指示是否可以将表达式计算为数字。

回答by MS Berends

I am sorry for the late answer (I am new on SO), but I think the correct answer is still missing. So I am afraid you are wrong too, simoco.

我很抱歉迟到的答案（我是新来的），但我认为仍然缺少正确的答案。所以恐怕你也错了，simoco。

For example, whenever I reference to a number in a pivot table where it is stored as text (source might be another table, or database), your function does not work. My solution seems to work in all cases. Excel already has a function for this which can be used as formula: =ISTEXT(reference).

例如，每当我引用存储为文本的数据透视表中的数字（源可能是另一个表或数据库）时，您的函数就不起作用。我的解决方案似乎适用于所有情况。Excel 已经有一个可以用作公式的函数：=ISTEXT(reference).

As the OP wants to use VBA, the solution is: Application.WorksheetFunction.IsText (reference), which returns Trueor False.

由于 OP 想要使用 VBA，解决方案是： Application.WorksheetFunction.IsText (reference)，它返回Trueor False。