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

excel, vba

Solution

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

Problem

Original source