reformat columns

[CandiC]

I am trying to find duplicate information in a spread sheet by using the
customer phone number. However, some phone numbers are formatted as
(563)345-678 and some are 563345678, so the conditional formatting is not
seeing this as a duplicate. Can someone please help?

 

[Sean Timmons]

if the format is phone number, find would locate them as 563345678. Do not
include the ()- in your search. If the phone numbers were entered as
(563)345-678 then you may want to do a find/replace for those characters and
remove them (Find ( replace with (leave blank), replace all.

 

[Chip Pearson]

If you don't want to alter the original data, insert a column next to
the original data, enter the following formula in the first data row
of the new column.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-",""),"
","")

and copy down as far as you need to go.

If you do want to change the original data, use

Sub FixPhone()
Dim R As Range
For Each R In Selection.Cells
R.Value = Replace( _
Replace( _
Replace( _
Replace(R.Value, _
"(", ""), ")", ""), "-", ""), " ", "")
Next R
End Sub

Select the cells to change and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)