This formula:
=OR(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
Say you have some stuff in A1, for example: asd123
the row(indirect()) resolves to this array:
{1;2;3;4;5;6}
Then the mid(a1,{1;2;3;4;5;6},1) is gonna just look at each of the characters in
that cell:
a, then s, then d, then 1, 2, 3
The hyphen in front of the mid (-mid()) converts anything that looks like a
number to a number. There's a difference between '1 and 1 (note the apostrophe
making the first text). And =mid() always returns text.
The isnumber() will look at the innards of that expression and return an array
that looks like:
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}
The =or() will return TRUE if there's at least one TRUE. Else it'll return
FALSE.
Be careful, though. You still have to array enter it (ctrl-shift-enter).
> Dave,
>
[quoted text clipped - 49 lines]
> >>
> >> Dave Peterson

Signature
Dave Peterson
Jay - 18 Oct 2006 09:58 GMT
Many thanks for taking the time to explain it fully Dave, I really
appreciate it. I'm trying to get to grips with the more arcane array
formulae & this is a perfect example to learn from.
Regards,
Jason
> This formula:
>
[quoted text clipped - 89 lines]
>> >>
>> >> Dave Peterson
Lori - 18 Oct 2006 10:01 GMT
To use advanced filter use the following list as a criteria range:
User Name
*0*
*1*
*2*
*3*
*4*
*5*
*6*
*7*
*8*
*9*
Dave Peterson - 18 Oct 2006 13:41 GMT
If I have cells that are real numbers (not text numbers), then this won't work.
> To use advanced filter use the following list as a criteria range:
>
[quoted text clipped - 9 lines]
> *8*
> *9*

Signature
Dave Peterson
Lori - 18 Oct 2006 14:11 GMT
That's true, but the context of the question was for a user name
starting with text.
If numbers were included in the range you could simpy add two extra
lines:
0
<>0
> If I have cells that are real numbers (not text numbers), then this won't work.
>
[quoted text clipped - 11 lines]
> > *8*
> > *9*
Lori - 18 Oct 2006 14:17 GMT
Apologies extra criteria for numeric data would be:
>=0
<0
> That's true, but the context of the question was for a user name
> starting with text.
[quoted text clipped - 19 lines]
> > > *8*
> > > *9*