Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat columns
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat columns
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. "CandiC" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformat columns
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) On Fri, 28 Aug 2009 10:58:01 -0700, CandiC wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
now() reformat in spreadsheet | Excel Worksheet Functions | |||
how to reformat numbers | Excel Discussion (Misc queries) | |||
reformat excel to letters in columns | Excel Discussion (Misc queries) | |||
Reformat of a table | Excel Discussion (Misc queries) | |||
reformat columns to rows? | Excel Worksheet Functions |