To select cells containing a certain character ?
 

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 )

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

To select cells containing a certain character ?
 

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.

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Mark246" wrote in message
ups.com...
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 )

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

To select cells containing a certain character ?
 

Or, you can filter
Data-Filter-Auto Filter
In the column that has the e-mail addresses:
Drop-down arrow, select (Custom)
Select contains
@

"Mark246" wrote:

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 )

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

To select cells containing a certain character ?
 

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



John Bundy wrote:
Easiest way is to do a helper column, in it put

To select cells containing a certain character ?
 

VERY COOL ! ! !

That one works, also.

Thanks a Bunch, guys.

Mark246


Sean Timmons wrote:
Or, you can filter

To select cells containing a certain character ?
 

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

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Mark246" wrote in message
ups.com...
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 )

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