Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion
From: MrMike on 30 Mar 2010 13:38
I have phone numbers in two different formats:
1. 1-123-456-7890 (11 digits w/dashes)
2. 1234567890 (10 digits only)

How can I convert the first format into the standard format in excel for
phone numbers of (###) ###-####?

I know the second format will do it automatically.

Thanks in advance for your help.
From: Bob Phillips on 30 Mar 2010 13:47
Try

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

--

HTH

Bob

"MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
>I have phone numbers in two different formats:
> 1. 1-123-456-7890 (11 digits w/dashes)
> 2. 1234567890 (10 digits only)
>
> How can I convert the first format into the standard format in excel for
> phone numbers of (###) ###-####?
>
> I know the second format will do it automatically.
>
> Thanks in advance for your help.


From: pmartglass on 30 Mar 2010 13:50
You can do a replace


ctrl + h

seek for -
leave replace blank

it will strip the area you select from all hyphens and then you can format
the same as the other data


"MrMike" wrote:

> I have phone numbers in two different formats:
> 1. 1-123-456-7890 (11 digits w/dashes)
> 2. 1234567890 (10 digits only)
>
> How can I convert the first format into the standard format in excel for
> phone numbers of (###) ###-####?
>
> I know the second format will do it automatically.
>
> Thanks in advance for your help.
From: MrMike on 30 Mar 2010 15:31
This removed the dashes, however I still have an 11 digit phone number with a
1 at the beginning, which I also would like to remove. Is there a better way
to do both? Meaning remove the dashes and the 1 before the number?

"pmartglass" wrote:

> You can do a replace
>
>
> ctrl + h
>
> seek for -
> leave replace blank
>
> it will strip the area you select from all hyphens and then you can format
> the same as the other data
>
>
> "MrMike" wrote:
>
> > I have phone numbers in two different formats:
> > 1. 1-123-456-7890 (11 digits w/dashes)
> > 2. 1234567890 (10 digits only)
> >
> > How can I convert the first format into the standard format in excel for
> > phone numbers of (###) ###-####?
> >
> > I know the second format will do it automatically.
> >
> > Thanks in advance for your help.
From: MrMike on 30 Mar 2010 15:34
This is also a good start, which is more of what I'm looking for, however I
now have a 4 digit area code because of the on before the number. Is there a
way to do this and remove just the 1 before the before number? All the other
functions I've tried removes all the ones in the phone number which is not
what I want to do.

"Bob Phillips" wrote:

> Try
>
> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>
> --
>
> HTH
>
> Bob
>
> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
> >I have phone numbers in two different formats:
> > 1. 1-123-456-7890 (11 digits w/dashes)
> > 2. 1234567890 (10 digits only)
> >
> > How can I convert the first format into the standard format in excel for
> > phone numbers of (###) ###-####?
> >
> > I know the second format will do it automatically.
> >
> > Thanks in advance for your help.
>
>
> .
>
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion