Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default Formatting Phone Numbers in Excel 2007 Help

Try

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

--

HTH

Bob

"MrMike" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default Formatting Phone Numbers in Excel 2007 Help

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

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" wrote in message
...
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.



.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default Formatting Phone Numbers in Excel 2007 Help

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" wrote in message
...
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.



.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 42
Default Formatting Phone Numbers in Excel 2007 Help

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" wrote in message
...
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.



.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Formatting Phone Numbers in Excel 2007 Help

Isn't it as simple as changing
=TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
to
=TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?

Why wouldn't that work?
--
David Biddulph


"MrMike" wrote in message
...
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" wrote in message
...
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.



.


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

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" wrote in message
...
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" wrote in message
...
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.


.


.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

Thanks for your help and direction, I used
=TEXT(RIGHT(TEXT(SUBSTITUTE(C2,"-",""),"#"),10),"(###) ###-####")
and just removed the extra - between the area code paranthesis and the
prefix of the phone number.

"pmartglass" wrote:

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" wrote in message
...
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.


.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

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" wrote in message
...
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" wrote in message
...
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.


.


.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Formatting Phone Numbers in Excel 2007 Help

Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an explanation of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
wrote:

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" wrote in message
...
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" wrote in message
...
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.


.


.


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default Formatting Phone Numbers in Excel 2007 Help

I use the code librarian that came with Excel 2000 Developer, but I always
have trouble re-installing it when I rebuild, so I am thinking of going with
KeyNote.

--

HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an explanation
of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
wrote:

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" wrote in message
...
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" wrote in message
...
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.


.


.




  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

Bob,
I don't see a code librarian in Excel 2007 under the Developer Tab. Did
they remove it or should I look somewhere else for it?
Thanks



"Bob Phillips" wrote:

I use the code librarian that came with Excel 2000 Developer, but I always
have trouble re-installing it when I rebuild, so I am thinking of going with
KeyNote.

--

HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an explanation
of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
wrote:

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" wrote in message
...
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" wrote in message
...
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.


.


.




.

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default Formatting Phone Numbers in Excel 2007 Help

No you won't, it was an extra that came with the Excel 2000 Developer
edition (which I still have).

--

HTH

Bob

"MrMike" wrote in message
...
Bob,
I don't see a code librarian in Excel 2007 under the Developer Tab. Did
they remove it or should I look somewhere else for it?
Thanks



"Bob Phillips" wrote:

I use the code librarian that came with Excel 2000 Developer, but I
always
have trouble re-installing it when I rebuild, so I am thinking of going
with
KeyNote.

--

HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an
explanation
of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in
an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
wrote:

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" wrote in message
...
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" wrote in message
...
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.


.


.




.





  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Formatting Phone Numbers in Excel 2007 Help

Do you know if that extra can be downloaded or purchased and added into Excel
2007?

"Bob Phillips" wrote:

No you won't, it was an extra that came with the Excel 2000 Developer
edition (which I still have).

--

HTH

Bob

"MrMike" wrote in message
...
Bob,
I don't see a code librarian in Excel 2007 under the Developer Tab. Did
they remove it or should I look somewhere else for it?
Thanks



"Bob Phillips" wrote:

I use the code librarian that came with Excel 2000 Developer, but I
always
have trouble re-installing it when I rebuild, so I am thinking of going
with
KeyNote.

--

HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an
explanation
of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in
an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
wrote:

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" wrote in message
...
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" wrote in message
...
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.


.


.




.



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting phone numbers LMR Excel Discussion (Misc queries) 5 March 4th 08 09:46 PM
Formatting phone numbers using Special or Custom ..Help!!!! HopeFromMS Excel Discussion (Misc queries) 2 January 3rd 08 03:11 PM
automatic formatting phone numbers in excel ApyoTech Excel Worksheet Functions 6 February 3rd 07 03:24 PM
Formatting Phone Numbers with Periods Cam Excel Discussion (Misc queries) 3 December 21st 06 02:49 PM
When merging phone numbers do not keep formatting why? Excel formatting troubles Excel Worksheet Functions 1 March 14th 06 11:30 PM


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"