Find specific text in a string

  • Thread starter Thread starter Kilcup
  • Start date Start date
K

Kilcup

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
 
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.
 
Hi Kilcup,

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

A safer criterion would be:

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

---
Norman


Norman Jones said:
Hi Kilcup,

Apply an autofilter, using (* as your criterion.
This will enable you to replace all the telephone numbers in one operation.
Click to expand...
 
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
 
Back
Top