CODEWITHSUNDEEP
excel
Adam_G
Adam_G

Reputation: 7879

Excel: Check if cell contains number in text string

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.

Upvotes: 7

Views: 46756

Answers (4)

Steven Martin
Steven Martin

Reputation: 3290

Similar to XOR LX's Answer but 2 chars shorter

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0

Upvotes: 15

Gary's Student
Gary's Student

Reputation: 96791

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

Upvotes: 2

XOR LX
XOR LX

Reputation: 7762

Not a very rigorous description, I'm afraid.

Perhaps:

=OR(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1)))

Regards

Upvotes: 3

chancea
chancea

Reputation: 5968

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)

Upvotes: 2

Related Questions