Advanced Filtering - text v numbers

O

Operation5PM

Hello,

I've got a worksheet with four columns, one of which is a user name. I
would like to take all the user names that contain numbers (and their
respective data) and put it into a separate worksheet. So far, I
haven't had any luck doing this with any combination of wildcard
operators.

The format of the user names are two or three letters and then 4
numbers, or they are entirely text. So the list has data like abc1234,
ab1234 and abcdef. I want all the abc1234 and ab1234 entries in a
separate list.

Any suggestions?

Thanks in advance.
 
D

Dave Peterson

Maybe you could use a helper column and filter by that:

With the username in A1, I could use this array formula in B1 (say):
=OR(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))


This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
O

Operation5PM

I see where you're going with this, however when I input the formula in
a new column all the entries returned false. The user names are in A1
so I'm not sure what the problem is at the moment. I'll keep tinkering.
Thanks.
 
D

Dave Peterson

Did you do this?
I see where you're going with this, however when I input the formula in
a new column all the entries returned false. The user names are in A1
so I'm not sure what the problem is at the moment. I'll keep tinkering.
Thanks.
Click to expand...
 
O

Operation5PM

I had to laugh at myself on that one - I just wasn't in the right field
at the time. Yes, it is working. Thank you verrrry much. You've saved
me a lot of time!
 
J

Jay

Dave,

Sorry about the late post. I've just caught this thread. I'm puzzled
by your formula. I see what it does buy could you explain *how*. I'd
really appreciate it. Many thanks,


Jason


I had to laugh at myself on that one - I just wasn't in the right field
at the time. Yes, it is working. Thank you verrrry much. You've saved
me a lot of time!
Click to expand...
 
D

Dave Peterson

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,

Sorry about the late post. I've just caught this thread. I'm puzzled
by your formula. I see what it does buy could you explain *how*. I'd
really appreciate it. Many thanks,

Jason
I had to laugh at myself on that one - I just wasn't in the right field
at the time. Yes, it is working. Thank you verrrry much. You've saved
me a lot of time!
Click to expand...
Click to expand...
 
J

Jay

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
 
L

Lori

To use advanced filter use the following list as a criteria range:

User Name
*0*
*1*
*2*
*3*
*4*
*5*
*6*
*7*
*8*
*9*
 
L

Lori

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
 
Top