#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default formula to format

I have a colume of phone numbers formatted as follows (123)123-1234. However
I need a formula to make a new colum exchanging the format to 123-123-1234.
Is there a way to do this? thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default formula to format

Are they numbers, displayed with a special format, or are they text strings?
--
David Biddulph

"Rick" wrote in message
...
I have a colume of phone numbers formatted as follows (123)123-1234.
However
I need a formula to make a new colum exchanging the format to
123-123-1234.
Is there a way to do this? thanks



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default formula to format

=SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
I have a colume of phone numbers formatted as follows (123)123-1234.
However
I need a formula to make a new colum exchanging the format to
123-123-1234.
Is there a way to do this? thanks



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default formula to format

they are numbers.

"Bernard Liengme" wrote:

=SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
I have a colume of phone numbers formatted as follows (123)123-1234.
However
I need a formula to make a new colum exchanging the format to
123-123-1234.
Is there a way to do this? thanks




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default formula to format

If they are numbers then you need a format setting not a formula! See
David's reply.

I type 1231231234 into cell
Called up cell format with CTRL+1; opened the Special group and selected
Phone Number
You can get the same effect with custom format:
[<=9999999]###-####;###-###-####
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
they are numbers.

"Bernard Liengme" wrote:

=SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
I have a colume of phone numbers formatted as follows (123)123-1234.
However
I need a formula to make a new colum exchanging the format to
123-123-1234.
Is there a way to do this? thanks








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default formula to format

Fixed Thanks a bunch and Happy NY

"Rick" wrote:

they are numbers.

"Bernard Liengme" wrote:

=SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rick" wrote in message
...
I have a colume of phone numbers formatted as follows (123)123-1234.
However
I need a formula to make a new colum exchanging the format to
123-123-1234.
Is there a way to do this? thanks




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
sum format in formula bar LovestheBOSOX Excel Discussion (Misc queries) 3 June 5th 07 02:07 AM
Conditional format formula Shu of AZ Excel Discussion (Misc queries) 1 November 26th 06 08:27 PM
Conditional Format & Formula Bob Excel Worksheet Functions 2 November 3rd 06 10:27 PM
Format\Formula Help scott Excel Worksheet Functions 7 April 14th 06 09:49 PM
formula based on format James Kendall Excel Discussion (Misc queries) 4 December 16th 04 05:47 PM


All times are GMT +1. The time now is 04:03 PM.

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"