View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Efficient way to drtermine if a string contains digits?

And Jim's solution (which I really like), returns FALSE for an empty cell.
--ron


Hit send too soon. My formula solution would need to be modified to do
that:

=IF(A1<"",NOT(ISNA(LOOKUP(9.9+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))) )


Here is a slightly shorter formula I came up with to do this...

=SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))) ,1),"1234567890")))0

What the SUMPRODUCT part is doing is summing up all the digits in A1, so
seeing if that is greater than zero gives the result the OP asked for.

Rick