The challenge this week is as follows: given an alphanumeric string of arbitrary length in A1, derive a single formula to return the number of numbers within that string which are divisible by either 3, 5 or 7.

By “divisible” here I mean of course that there is no remainder after division.

And by “numbers within that string” I mean all consecutive substrings of any length within that string which may be interpreted as a number. (It can also safely be assumed that there are no alphanumeric combinations within the string in A1 which would be interpreted by Excel as numeric, e.g. JAN01.)

For example, the string:

XX30X5XXX42XX771

contains, by this definition, 13 numbers: 3, 0, 30, 5, 4, 2, 42, 7, 7, 1, 77, 71 and 771.

The answer for the above string would be 9, since:

3 IS divisible by 3
0 IS divisible by 3, 5 and 7
30 IS divisible by 3 or 5
5 IS divisible by 5
4 IS NOT divisible by any of 3, 5 or 7
2 IS NOT divisible by any of 3, 5 or 7
42 IS divisible by 3 or 7
7 IS divisible by 7
7 IS divisible by 7
1 IS NOT divisible by any of 3, 5 or 7
77 IS divisible by 7
71 IS NOT divisible by any of 3, 5 or 7
771 IS divisible by 3

Solution next week. Best of luck!