Find specific text in a string
 

Dear friends,

I am attempting to use a macro to locate cells that contain phon
numbers. The phone numbers follow this format: (XXX)XXX-XXXX and ar
strings. Also, all of the numbers are in column A.

Here is how the info is listed:

Joe Samuel
123 Lilly Lane
Your town AL 55555
(XXX)XXX-XXXX
Sally Smart
3456 Her Lane
My Town AL 55555
(XXX)XXX-XXXX

I am trying to create code that will a) locate the fields that contai
the phone numbers and then b) clear that cell, moving on to the nex
cell, etc. I suppose the best thing to do would be simply to locat
fields where the first character is a parenthesis, then clear it.

Can anyone help?

Thanks

--
Message posted from http://www.ExcelForum.com

Find specific text in a string
 

Dim rng as Range, cell as Range
set rng = Columns(1).SpecialCells(xlConstants, xlTextValues)
for each cell in rng
if left(cell.Text,1).Value = "(" then
cell.ClearContents
end if
Next

assumes that the telephone numbers are entered as strings as shown and the
(XXX)XXX-XXXX appearance is not produced by formatting.

--
Regards,
Tom Ogilvy

"Kilcup " wrote in message
...
Dear friends,

I am attempting to use a macro to locate cells that contain phone
numbers. The phone numbers follow this format: (XXX)XXX-XXXX and are
strings. Also, all of the numbers are in column A.

Here is how the info is listed:

Joe Samuel
123 Lilly Lane
Your town AL 55555
(XXX)XXX-XXXX
Sally Smart
3456 Her Lane
My Town AL 55555
(XXX)XXX-XXXX

I am trying to create code that will a) locate the fields that contain
the phone numbers and then b) clear that cell, moving on to the next
cell, etc. I suppose the best thing to do would be simply to locate
fields where the first character is a parenthesis, then clear it.

Can anyone help?

Thanks.


---
Message posted from http://www.ExcelForum.com/

Find specific text in a string
 

Hi Kilcup,

Apply an autofilter, using (* as your criterion.
This will enable you to replace all the telephone numbers in one operation.

---
Regards,
Norman

"Kilcup " wrote in message
...
Dear friends,

I am attempting to use a macro to locate cells that contain phone
numbers. The phone numbers follow this format: (XXX)XXX-XXXX and are
strings. Also, all of the numbers are in column A.

Here is how the info is listed:

Joe Samuel
123 Lilly Lane
Your town AL 55555
(XXX)XXX-XXXX
Sally Smart
3456 Her Lane
My Town AL 55555
(XXX)XXX-XXXX

I am trying to create code that will a) locate the fields that contain
the phone numbers and then b) clear that cell, moving on to the next
cell, etc. I suppose the best thing to do would be simply to locate
fields where the first character is a parenthesis, then clear it.

Can anyone help?

Thanks.


---
Message posted from http://www.ExcelForum.com/

Find specific text in a string
 

Hi Kilcup,

A safer criterion would be:

(???)???-????

---
Norman


"Norman Jones" wrote in message
...
Hi Kilcup,

Apply an autofilter, using (* as your criterion.
This will enable you to replace all the telephone numbers in one
operation.

---
Regards,
Norman

"Kilcup " wrote in message
...
Dear friends,

I am attempting to use a macro to locate cells that contain phone
numbers. The phone numbers follow this format: (XXX)XXX-XXXX and are
strings. Also, all of the numbers are in column A.

Here is how the info is listed:

Joe Samuel
123 Lilly Lane
Your town AL 55555
(XXX)XXX-XXXX
Sally Smart
3456 Her Lane
My Town AL 55555
(XXX)XXX-XXXX

I am trying to create code that will a) locate the fields that contain
the phone numbers and then b) clear that cell, moving on to the next
cell, etc. I suppose the best thing to do would be simply to locate
fields where the first character is a parenthesis, then clear it.

Can anyone help?

Thanks.


---
Message posted from http://www.ExcelForum.com/

Find specific text in a string
 

Dear Tom,

Thank you for your reply! I am getting an error message on th
following line of the code that I copied from your reply:

If Left(cell.Value, 1).Value = "(" Then

This is the error message I received:

Runtime error '424'
Object Required

Thank you again for any help you can provide.

Jef

--
Message posted from http://www.ExcelForum.com

Find specific text in a string
 

Try
IF LEFT(cell.Value,1) = "(" Then
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kilcup " wrote in message ...
Dear Tom,

Thank you for your reply! I am getting an error message on the
following line of the code that I copied from your reply:

If Left(cell.Value, 1).Value = "(" Then

This is the error message I received:

Runtime error '424'
Object Required

Thank you again for any help you can provide.

Jeff


---
Message posted from http://www.ExcelForum.com/