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 |