Formulas for telephone numbers: finding duplicates, autoformat

[Sandeep Elbak]

- How can I check (I need a formula) if a column contains same telephone
number twice or not? It is too slow to check manually (data / sort)

- How could I autoformat telephone numbers, like 040123456 => 040-123
456, so the numbers would be easier to read on screen? I will not copy
phone numbers to any other application, it is sufficient the numbers
will be readable in Excel, so no hard space bars are necessary.

 

[Bob Phillips]

Add a formula in B1

=IF(COUNTIF($A$1:A1,A1)>1,"Dup","")

and copy down.

Custom format (Format>Cells>Custom) of 000-000000

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Stevie_mac]

For formatting, right click a cell, select "Format Cells".
On the "Number Tab" select "Custom" from the "Category" list
Enter 000-000-000 as the format (in the text box below to the right)

For finding duplicates, you could use a a simple CountIf
EG.
Enter a list of phone numbers in Col A
Enter the function =COUNTIF($A$1:$A$500, A1) in B1
Copy B1 down as far as your list is long
If any cell in Col B is > 1 then it is a duplicate.

 

[David McRitchie]

Hi Sandeep,

Here is little more Conditional Formatting to indicate duplicates
with identification of row location of the original phone number

Example in: http://www.mvps.org/dmcritchie/excel/condfmt.htm#duplicates

Duplicate identifications
(yellowish first among duplicates, greenish for actual duplicates)
formula 1 is: =IF(COUNTIF($A:$A,$A1)>1,COUNTIF($A$1:$A1,$A1)=1)
formula 2 is: =IF(COUNTIF($A:$A,$A1)>1,COUNTIF($A$1:$A1,$A1)>1)

Identification of the row number for the original value (somewhere on row 1)
=IF(COUNTIF($A$1:$A1,$A1)>1,MATCH($A1,$A$1:$A1,0),"")

But the identification formula would start somewhere on the second row
if you had column headers on row 1.
=IF(COUNTIF($A$1:$A2,$A2)>1,MATCH($A2,$A$1:$A2,0),"")
as if you started on row 1 with the original, because you want to
identify the actual row number of the original.

And use of the fill handle to copy formulas down
http://www.mvps.org/dmcritchie/excel/fillhand.htm