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

 

[Rick Rothstein]

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.

Just to point out... you do not need that IF function call since just using what Biff posted directly, namely this...

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

will automatically return TRUE or FALSE to the cell.

 

[Gingertrees]

EDIT: using anything more than ">0" breaks down in cases of repeated numbers. So, T. Valko's original >0 is superior. Sorry I didn't figure that out before I could change my post. Revised:
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0

 

[Michael Avidan]

For True/False - try also:

=OR(COUNT(FIND(ROW($1:$11)-1,A1)))

This is an Array Formula to be entered with <ctrl+shift+enter>Ctrl+Shift+Enter
-----------------------------
Micky</ctrl+shift+enter>

 

[Rick Rothstein]

For True/False - try also:

=OR(COUNT(FIND(ROW($1:$11)-1,A1)))

This is an Array Formula to be entered with <ctrl+shift+enter>Ctrl+Shift+Enter

Not sure if you are aware of it or not, but you responded to a question that is more than 4.5 years old. Also, I am thinking that your proposed formula would not be as efficient as the one I or Gingertrees proposed at the end of the thread... your formula involves two additional function calls for Excel to evaluate plus it is an array-entered formula as opposed to a normally-entered one. On top of that, I think the ROW($1:$11)-1 part of your code should be ROW($1:$10)-1 if you are trying to create the array 0 though 9. Finally, you should use a cell to cell reference (such as $A$1:$A$10) instead of $1:$10 inside that ROW function call. The reason is the number of precedents Excel has to track for your formula (even though you don't care what is in those cells, Excel doesn't know that). To see this, put your originally proposed formula in a cell, select that cell, go into the VBA editor (ALT+F11) and execute this line of code in the Immediate Window (CNTRL+G if you don't see it)...

Print Selection.Precedents.Count

It should print out 180224 (that is 180,224 cells)! Now change the $1:$11 to $A$1:$A$11 and rerun the above line of code. It should now print out 11... that is much less for Excel to track. By the way, if you check, you will find the Precedent count for the formula I and Gingertrees proposed is 1.

 

[Michael Avidan]

Thank you, Rick for your highlights.
I didn't realized it is such an "Old" thread.
The $1:$11 was, of course, a TYPO on my side.
Your suggestion (and proof beside it) to use A$1:A$10 instead will be fully adopted.
--------------------
Micky