Advanced Filtering - text v numbers

[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.

 

[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.)

 

[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.

 

[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.

 

[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!

 

[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!

 

[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!

 

[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

 

[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*

 

[Dave Peterson]

If I have cells that are real numbers (not text numbers), then this won't work.

 

[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

 

[Lori]

Apologies extra criteria for numeric data would be:

=0

<0