Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2006

Tip: Looking for answers? Try searching our database.

Advanced Filtering - text v numbers

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Operation5PM - 06 Oct 2006 16:09 GMT
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 - 06 Oct 2006 16:18 GMT
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.)

> Hello,
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Operation5PM - 06 Oct 2006 16:48 GMT
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.

> Maybe you could use a helper column and filter by that:
>
[quoted text clipped - 21 lines]
> >
> > Thanks in advance.
Dave Peterson - 06 Oct 2006 16:50 GMT
Did you do this?

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

> 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
[quoted text clipped - 30 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Operation5PM - 06 Oct 2006 18:08 GMT
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!

> Did you do this?
>
[quoted text clipped - 36 lines]
> > >
> > > Dave Peterson
Jay - 17 Oct 2006 21:21 GMT
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
[quoted text clipped - 38 lines]
>>>>
>>>> Dave Peterson
Dave Peterson - 17 Oct 2006 23:25 GMT
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*
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.