Formulas for telephone numbers: finding duplicates, autoformat

- 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.

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)


"Sandeep Elbak" <Sandeep.Elbak@invalid.com> wrote in message
news:%23uar560TFHA.2872@TK2MSFTNGP14.phx.gbl...
> - 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.

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.




"Sandeep Elbak" <Sandeep.Elbak@invalid.com> wrote in message news:%23uar560TFHA.2872@TK2MSFTNGP14.phx.gbl...
>- 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.

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...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
---
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

"Sandeep Elbak" <Sandeep.Elbak@invalid.com> wrote in message news:%23uar560TFHA.2872@TK2MSFTNGP14.phx.gbl...
> - 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.