To select cells containing a certain character ?

[Mark246]

Hi, People,

I've got a list, one column, about 5,000 rows, containing...
Name (John Smith)
Address
maybe another address line (or this row doesn't exist)
City, etc...
Email address ([email protected])

How can I select Only the rows that have email addresses?... Only the
rows that have the @ character?

Using Excel 2000, or I can easily move the data to Word 2000, or a
simple text file.

Any ideas?

Thanks a bunch.

Mark246

 

[John Bundy]

Easiest way is to do a helper column, in it put
=IF(ISERROR(FIND("@",A1)),"","1")
and copy down, there will be a 1 in each row that has @. copy the column,
paste special values, then sort by that column and it will group them all
together.

 

[Mark246]

BRILLIANT ! I Love It ! ! !

Works like a charm.

I had a bit of trouble understanding your terse verbiage,
(never heard of a Helper column)
(Copy Down means copy that formula thru the next 5,000 cells)
but I finally understood it.

Thanks a LOT, John. It's TERRIFIC !

Mark246

 

[Mark246]

VERY COOL ! ! !

That one works, also.

Thanks a Bunch, guys.

Mark246

 

[John Bundy]

Get your myNum from anywhere you want and I output to a msgbox but you can
output anywhere, ask if you have a question.
Sub main()
Dim myArray(30, 1) As Long
Dim myIndex As Long
Dim myRow As Long
Dim myNum As Long

myNum = 4


For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row 'change i to first
'row and "A" to first column
myArray(i, 1) = Cells(i, 1)
myRow = myRow + 1
Next


For i = 1 To UBound(myArray)
MsgBox myArray(i, 1)
If myArray(i, 1) = myNum Then MsgBox "USA"
Next

End Sub