Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion
From: pmartglass on 30 Mar 2010 15:51
how about
=right(TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####"),10)

"MrMike" wrote:

> 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.
> >
> >
> > .
> >
From: pmartglass on 30 Mar 2010 16:02
in thinking more about it, it would need to look like this

=TEXT(RIGHT(TEXT(SUBSTITUTE(B8,"-",""),"#"),10),"(###)-###-####")

hope this helps

"MrMike" wrote:

> 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.
> >
> >
> > .
> >
From: "David Biddulph" groups [at] on 30 Mar 2010 16:12
Isn't it as simple as changing
=TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
to
=TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?

Why wouldn't that work?
--
David Biddulph


"MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
> 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.
>>
>>
>> .
>>

From: MrMike on 30 Mar 2010 16:51
Yes, this is the one I used, thanks.



"David Biddulph" wrote:

> Isn't it as simple as changing
> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> to
> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
>
> Why wouldn't that work?
> --
> David Biddulph
>
>
> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
> > 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.
> >>
> >>
> >> .
> >>
>
> .
>
From: MrMike on 30 Mar 2010 17:12
One more thing, is there anyway to save this in excel so I can get to it
quickly without having to refer to any notes?


"David Biddulph" wrote:

> Isn't it as simple as changing
> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> to
> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
>
> Why wouldn't that work?
> --
> David Biddulph
>
>
> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
> > 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.
> >>
> >>
> >> .
> >>
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion