I have a worksheet with text strings in each cell (article title). I'd like to know if a cell contains a number. For example:
'This is 3' --> TRUE
'Red balloon' --> FALSE
' It's 10 things' --> TRUE
Update: Every answer on here works. I just chose the shortest and simplest.
Similar to XOR LX's Answer but 2 chars shorter
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0
Not a very rigorous description, I'm afraid.
Perhaps:
=OR(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1)))
Regards
Here is one formula that would do it using average:
=LEN(A1)<>AVERAGE((LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))
or you could use small:
=LEN(A1)<>SMALL(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")),1)
If you need a VBA function:
Public Function NumberInThere(r As Range)
Dim v As String, L As Long, i As Long
NumberInThere = False
v = r.Text
L = Len(v)
For i = 1 To L
If IsNumeric(Mid(v, i, 1)) Then
NumberInThere = True
End If
Next i
End Function
