if a string contains a number then return true

[tech2]

I would appreciate help on a formula that would return 'TRUE' if the string contains a number. Example below. Using Excel 2007.

One Part Order 123456 for shortage items shipping to US/TX-USA

Thanks,

tech2

 

[HarryS]

try the function below in a code module

in a cell as = HasNumber(B4) etc

Function HasNumber(w$) As Boolean
    Dim p%
    While Not HasNumber And p < Len(w)
        p = p + 1
        If Mid(w, p, 1) >= "0" And Mid(w, p, 1) <= "9" Then HasNumber = True
    Wend
End Function

 

[mikerickson]

Try
=IF(SUMPRODUCT(LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))<>10*LEN(A2), "contains numeral", "no numerals")

 

[Rick Rothstein]

This formula will return TRUE if the text contains a number and FALSE if not...

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))<=LEN(A1)

 

[tech2]

Thanks Rick, this is exactly what I was looking for.

 

[T. Valko]

I would appreciate help on a formula that would return 'TRUE' if the string contains a number. Example below. Using Excel 2007.

One Part Order 123456 for shortage items shipping to US/TX-USA

Thanks,

tech2

Here's another one...

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

 

[Rick Rothstein]

Here's another one...

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

And a good one it is!

 

[Biz]

Here's another one...

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

Nice little formula.

Biz

 

[T. Valko]

And a good one it is!

Nice little formula.

Biz

Thanks, guys!

 

[Gingertrees]

Thanks for the great formula - however, I was dismayed when Excel informed me that I could not use arrays in a conditional formatting/data validation statement. HOWEVER, I wanted to post how I successfully got around that:

I wanted to make sure that A1 had at least 4 numbers. E.g. A98765, BC01234, etc.

In a column that would be hidden, say P, I placed this formula in cell P1:
=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>4,TRUE,FALSE)

Then in some other cell, say P2, I placed the formula:
=P1

For my data validation, I selected Custom --> Formula, and placed this formula:
=P2=TRUE

I hope someone finds this tidbit and it saves him/her some time.