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

Question

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

Answer 1

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 Text format and then you change Text format 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 F2 and 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.

So, examine Range.NumberFormat is a wrong way.

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 A1 contains number "123" stored as text. In this case Range("A1").Value + "0" would concatenate two strings and return "1230", but if A1 contains number 123 stored as number, "0" would be casted to 0 and Range("A1").Value + "0" would return number 123

Result:

Answer 2

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.

Answer 3

The test you seek is

ISNUMERIC(...)

From Excel help:

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

Answer 4

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

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 General and the number is stored as Text using '

EDIT:

To demonstrate this a little further. We will convert the above sub to a 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