tricky "sort" needed

  • Thread starter Thread starter jvoortman
  • Start date Start date
J

jvoortman

I have a list of data going down column A, and I was
wondering if there was a way that I could get it to either
bunch together all cells that have a "@" in the text or
eliminate all celles without a "@". I want to extract the
email addresses out of the rest of the crud.

sample:

Brabner & Hollon, Inc.
3053 Cotton Street
Mobile, AL 36607
(251) 479-5408
FAX: 251-479-5403
Email: (e-mail address removed)
Website: www.brabnerhollon.com

Building Specialties Company, Inc.
Paul Caddell
P.O. Box 788
Birmingham, AL 35201
(205) 956-1600
FAX: 205-956-1655
Email: (e-mail address removed)
Website: www.bscoinc.com

Construction Specialties of Montgomery
Brett Leary
P.O. Box 210879
Montgomery, AL 36121-0879
(334) 277-1629
FAX: 334-277-1619
Email: (e-mail address removed)

Paper Products of Mobile
John Eskridge
P.O. Box 7066
Mobile, AL 36670
(251) 473-4431
FAX: 251-478-1373
Email: (e-mail address removed)
Website: www.paperman.org

EFG Construction Specialties
Gail Bryant or Eddie Kah
463 Pike Road
Piedmont, AL 36272
(256) 492-4208
FAX: 256-547-9360
 
Could you apply Data|Filter|Autofilter to column A.

Then apply a custom filter of contains @ and copy and paste to a different
worksheet?
 
Hi

One way is to generate a helper column which will identify
any item that contains a "@". One formula would be
=ISERR(FIND("@",A1,1))
You could then use an autofilter to find all the false
entries then copy to another area, or use the advanced
filter to automatically move to another sheet.

Tony
 
huh? a bit dozy here

-----Original Message-----
Could you apply Data|Filter|Autofilter to column A.

Then apply a custom filter of contains @ and copy and paste to a different
worksheet?




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
simplest (although less elegant) is to use:
if data is in column A starting at A1, put the following
formula in B1:

=ISERR(FIND("@",A1))

copy down as needed. This will give a series of TRUE and
FALSE results. Sort using column B to "bunch" TRUE's and
FALSE's
 
-----Original Message-----
I have a list of data going down column A, and I was
wondering if there was a way that I could get it to either
bunch together all cells that have a "@" in the text or
eliminate all celles without a "@". I want to extract the
email addresses out of the rest of the crud.

sample:

Brabner & Hollon, Inc.
3053 Cotton Street
Mobile, AL 36607
(251) 479-5408
FAX: 251-479-5403
Email: (e-mail address removed)
Website: www.brabnerhollon.com

Building Specialties Company, Inc.
Paul Caddell
P.O. Box 788
Birmingham, AL 35201
(205) 956-1600
FAX: 205-956-1655
Email: (e-mail address removed)
Website: www.bscoinc.com

Construction Specialties of Montgomery
Brett Leary
P.O. Box 210879
Montgomery, AL 36121-0879
(334) 277-1629
FAX: 334-277-1619
Email: (e-mail address removed)

Paper Products of Mobile
John Eskridge
P.O. Box 7066
Mobile, AL 36670
(251) 473-4431
FAX: 251-478-1373
Email: (e-mail address removed)
Website: www.paperman.org

EFG Construction Specialties
Gail Bryant or Eddie Kah
463 Pike Road
Piedmont, AL 36272
(256) 492-4208
FAX: 256-547-9360
Click to expand...
I attempted a simple sort of the data and got all of the
emails grouped together (see below). A simple editing job
on the text you don't need (Email:) will render the
results you want.

RESULTS;
(205) 956-1600
(251) 473-4431
(251) 479-5408
(256) 492-4208
(334) 277-1629
.
3053 Cotton Street
463 Pike Road
Birmingham, AL 35201
Brett Leary
Building Specialties Company, Inc.
Construction Specialties of Montgomery
EFG Construction Specialties
Email: (e-mail address removed)
Email: (e-mail address removed)
Email: (e-mail address removed)
Email: (e-mail address removed)
FAX: 205-956-1655
FAX: 251-478-1373
FAX: 251-479-5403
FAX: 256-547-9360
FAX: 334-277-1619
Gail Bryant or Eddie Kah
John Eskridge
Mobile, AL 36607
Mobile, AL 36670
Montgomery, AL 36121-0879
P.O. Box 210879
P.O. Box 7066
P.O. Box 788
Paper Products of Mobile
Paul Caddell
Piedmont, AL 36272
Website: www.brabnerhollon.com
Website: www.bscoinc.com
Website: www.paperman.org
Click to expand...
Brabner & Hollon, Inc.
 
It sounds like you have enough solutions, but if you've never used
Data|filter|autofilter, you're missing one of the easiest and most powerful
tools in excel.

Say your data is in Column A.
Select column A
Then from the menubar: Data|Filter|Autofilter.

The first used cell in column A will now have a little dropdown arrow on it. If
you click on that little arrow, you'll see all the values in that filtered range
(actually only 1000 unique entries show).

You can select a value from that list and show all the rows that match your
criteria.

But you may have noticed (near the top of that list) was Custom. You can click
on that and specify up to 2 different criteria. (greater than 10 AND less than
1000 -- or equal to Illinois OR equal to Wisconsin).

But I suggested using Contains and the character @. Only the cells that contain
@ will be shown. Now you can copy and paste that range to another location.

You can actually select a larger range (include the last row of headers and
apply Data|filter|autofilter) to that bigger range.

Now you can filter by different columns. With each filter applied, the list
gets shorter (it's cummulative).

When you're ready to see everything again, you can go through each drowdown
arrow and select All for that column (or just Data|filter|ShowAll).

Excel does try to help you by shading the dropdown blue when the filter is
active. (It also shades the row numbers of that filtered range when a filter is
in effect.

Debra Dalgleish has some neat tips and pictures at:
http://www.contextures.com/xlautofilter01.html
 
Back
Top