PC Review


Reply
Thread Tools Rate Thread

How do I format phone numbers in excel?

 
 
=?Utf-8?B?Q0JpY2tsZXk=?=
Guest
Posts: n/a
 
      23rd Aug 2006
I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
the xxx-xxx-xxxx format but am unable to do so with the format cells. What
do I need to do?
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      23rd Aug 2006
If those () and -'s are really part of the cell (not formatting choices),

Then select the column
and do 3 edit|replaces

edit|replace
what: (
with: (leave blank)
replace all

edit|replace
what: )
with: (leave blank)
replace all

edit|replace
what: -
with: (leave blank)
replace all

Now all your entries should be real numbers and your format|cells|number tab
choices should work ok.


CBickley wrote:
>
> I have an excel file with a column for phone numbers. Some are (xxx)
> xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> do I need to do?


--

Dave Peterson
 
Reply With Quote
 
 
 
 
=?Utf-8?B?VG9wcGVycw==?=
Guest
Posts: n/a
 
      23rd Aug 2006
If 'phone number in column A, then put this in B and copy down:

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")

HTH

"CBickley" wrote:

> I have an excel file with a column for phone numbers. Some are (xxx)
> xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> do I need to do?

 
Reply With Quote
 
kfogle
Guest
Posts: n/a
 
      23rd Aug 2006
Format Cells > Choose "Custom" Category > In the "Type:" field on the
right type in "000-000-0000"

 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      23rd Aug 2006
Edit>Replace

what: (
with: nothing
replace all

Same for )


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 10:44:01 -0700, CBickley
<(E-Mail Removed)> wrote:

>I have an excel file with a column for phone numbers. Some are (xxx)
>xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
>the xxx-xxx-xxxx format but am unable to do so with the format cells. What
>do I need to do?


 
Reply With Quote
 
=?Utf-8?B?Q0JpY2tsZXk=?=
Guest
Posts: n/a
 
      23rd Aug 2006
Thanks, this worked. Now I have a space in the middle of the numbers that I
need to delete (xxx xxxxxxx). How do I do this?

"Dave Peterson" wrote:

> If those () and -'s are really part of the cell (not formatting choices),
>
> Then select the column
> and do 3 edit|replaces
>
> edit|replace
> what: (
> with: (leave blank)
> replace all
>
> edit|replace
> what: )
> with: (leave blank)
> replace all
>
> edit|replace
> what: -
> with: (leave blank)
> replace all
>
> Now all your entries should be real numbers and your format|cells|number tab
> choices should work ok.
>
>
> CBickley wrote:
> >
> > I have an excel file with a column for phone numbers. Some are (xxx)
> > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> > do I need to do?

>
> --
>
> Dave Peterson
>

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      23rd Aug 2006
Add one more edit|replace

Edit|Replace
what: (spacebar)
with: (leave blank)
replace all

CBickley wrote:
>
> Thanks, this worked. Now I have a space in the middle of the numbers that I
> need to delete (xxx xxxxxxx). How do I do this?
>
> "Dave Peterson" wrote:
>
> > If those () and -'s are really part of the cell (not formatting choices),
> >
> > Then select the column
> > and do 3 edit|replaces
> >
> > edit|replace
> > what: (
> > with: (leave blank)
> > replace all
> >
> > edit|replace
> > what: )
> > with: (leave blank)
> > replace all
> >
> > edit|replace
> > what: -
> > with: (leave blank)
> > replace all
> >
> > Now all your entries should be real numbers and your format|cells|number tab
> > choices should work ok.
> >
> >
> > CBickley wrote:
> > >
> > > I have an excel file with a column for phone numbers. Some are (xxx)
> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> > > do I need to do?

> >
> > --
> >
> > Dave Peterson
> >


--

Dave Peterson
 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      23rd Aug 2006
I'm not sure why OP needs any more than two edit>replaces to get rid of the ()

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

Edit>Replace

what: (
with: nothing

Edit>Replace

what: )
with: - My first post was replace with nothing which was incorrect.

returns xxx-xxx-xxxx

I guess I don't understand why the need to trun the phone number into a number
just to turn around and custom format as

xxx-xxx-xxxx


Gord

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <(E-Mail Removed)>
wrote:

>Add one more edit|replace
>
>Edit|Replace
>what: (spacebar)
>with: (leave blank)
>replace all
>
>CBickley wrote:
>>
>> Thanks, this worked. Now I have a space in the middle of the numbers that I
>> need to delete (xxx xxxxxxx). How do I do this?
>>
>> "Dave Peterson" wrote:
>>
>> > If those () and -'s are really part of the cell (not formatting choices),
>> >
>> > Then select the column
>> > and do 3 edit|replaces
>> >
>> > edit|replace
>> > what: (
>> > with: (leave blank)
>> > replace all
>> >
>> > edit|replace
>> > what: )
>> > with: (leave blank)
>> > replace all
>> >
>> > edit|replace
>> > what: -
>> > with: (leave blank)
>> > replace all
>> >
>> > Now all your entries should be real numbers and your format|cells|number tab
>> > choices should work ok.
>> >
>> >
>> > CBickley wrote:
>> > >
>> > > I have an excel file with a column for phone numbers. Some are (xxx)
>> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
>> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
>> > > do I need to do?
>> >
>> > --
>> >
>> > Dave Peterson
>> >


Gord Dibben MS Excel MVP
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      23rd Aug 2006
I think that there was an unfortunate line break in the middle of his examples:

I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx.

I think the first example was really:
(xxx) xxx-xxxx
So there would have been an extra space in that string.

And as a personal choice, I'd want all my data the same format--either all text
or all numbers (nicely formatted).

I'd convert any text strings xxx-xxx-xxxx to its number and then use
format|cells to give the whole range a nice consistent look.


Gord Dibben wrote:
>
> I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
>
> Some data looks like (xxx)xxx-xxxx
>
> Wants it to look like xxx-xxx-xxxx
>
> Edit>Replace
>
> what: (
> with: nothing
>
> Edit>Replace
>
> what: )
> with: - My first post was replace with nothing which was incorrect.
>
> returns xxx-xxx-xxxx
>
> I guess I don't understand why the need to trun the phone number into a number
> just to turn around and custom format as
>
> xxx-xxx-xxxx
>
> Gord
>
> On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <(E-Mail Removed)>
> wrote:
>
> >Add one more edit|replace
> >
> >Edit|Replace
> >what: (spacebar)
> >with: (leave blank)
> >replace all
> >
> >CBickley wrote:
> >>
> >> Thanks, this worked. Now I have a space in the middle of the numbers that I
> >> need to delete (xxx xxxxxxx). How do I do this?
> >>
> >> "Dave Peterson" wrote:
> >>
> >> > If those () and -'s are really part of the cell (not formatting choices),
> >> >
> >> > Then select the column
> >> > and do 3 edit|replaces
> >> >
> >> > edit|replace
> >> > what: (
> >> > with: (leave blank)
> >> > replace all
> >> >
> >> > edit|replace
> >> > what: )
> >> > with: (leave blank)
> >> > replace all
> >> >
> >> > edit|replace
> >> > what: -
> >> > with: (leave blank)
> >> > replace all
> >> >
> >> > Now all your entries should be real numbers and your format|cells|number tab
> >> > choices should work ok.
> >> >
> >> >
> >> > CBickley wrote:
> >> > >
> >> > > I have an excel file with a column for phone numbers. Some are (xxx)
> >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> >> > > do I need to do?
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >> >

>
> Gord Dibben MS Excel MVP


--

Dave Peterson
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      23rd Aug 2006
And by having the format of the field consistent, I can use =vlookup() and
=match() without having to worry about if the data is text or numeric.

Dave Peterson wrote:
>
> I think that there was an unfortunate line break in the middle of his examples:
>
> I have an excel file with a column for phone numbers. Some are (xxx)
> xxx-xxxx and others are xxx-xxx-xxxx.
>
> I think the first example was really:
> (xxx) xxx-xxxx
> So there would have been an extra space in that string.
>
> And as a personal choice, I'd want all my data the same format--either all text
> or all numbers (nicely formatted).
>
> I'd convert any text strings xxx-xxx-xxxx to its number and then use
> format|cells to give the whole range a nice consistent look.
>
> Gord Dibben wrote:
> >
> > I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
> >
> > Some data looks like (xxx)xxx-xxxx
> >
> > Wants it to look like xxx-xxx-xxxx
> >
> > Edit>Replace
> >
> > what: (
> > with: nothing
> >
> > Edit>Replace
> >
> > what: )
> > with: - My first post was replace with nothing which was incorrect.
> >
> > returns xxx-xxx-xxxx
> >
> > I guess I don't understand why the need to trun the phone number into a number
> > just to turn around and custom format as
> >
> > xxx-xxx-xxxx
> >
> > Gord
> >
> > On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <(E-Mail Removed)>
> > wrote:
> >
> > >Add one more edit|replace
> > >
> > >Edit|Replace
> > >what: (spacebar)
> > >with: (leave blank)
> > >replace all
> > >
> > >CBickley wrote:
> > >>
> > >> Thanks, this worked. Now I have a space in the middle of the numbers that I
> > >> need to delete (xxx xxxxxxx). How do I do this?
> > >>
> > >> "Dave Peterson" wrote:
> > >>
> > >> > If those () and -'s are really part of the cell (not formatting choices),
> > >> >
> > >> > Then select the column
> > >> > and do 3 edit|replaces
> > >> >
> > >> > edit|replace
> > >> > what: (
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > edit|replace
> > >> > what: )
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > edit|replace
> > >> > what: -
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > Now all your entries should be real numbers and your format|cells|number tab
> > >> > choices should work ok.
> > >> >
> > >> >
> > >> > CBickley wrote:
> > >> > >
> > >> > > I have an excel file with a column for phone numbers. Some are (xxx)
> > >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> > >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> > >> > > do I need to do?
> > >> >
> > >> > --
> > >> >
> > >> > Dave Peterson
> > >> >

> >
> > Gord Dibben MS Excel MVP

>
> --
>
> Dave Peterson


--

Dave Peterson
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format of phone numbers automatically chaning format =?Utf-8?B?bGV5c3k=?= Microsoft Outlook Contacts 1 29th Apr 2007 12:27 AM
GET $50 CASH EVERY FREE CELL PHONE YOU GIVE AWAY or GET A FREE CELL PHONE or CAMERA PHONE online_store_4@yahoo.com Freeware 1 13th Feb 2006 03:40 PM
How to move numbers in Contacts phone field to another phone field =?Utf-8?B?Rm9sYQ==?= Microsoft Outlook Contacts 1 14th Jun 2005 09:52 AM
How can I cross reference phone numbers with existing phone numbe. =?Utf-8?B?Sm9obg==?= Microsoft Excel Misc 1 11th Feb 2005 05:39 PM
Can I upload ONLY names and phone numbers to Blackberry phone W/O. =?Utf-8?B?R2VvcmdlIExlYWs=?= Microsoft Outlook Contacts 0 3rd Feb 2005 04:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:06 PM.