Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting phone numbers | Excel Discussion (Misc queries) | |||
Formatting phone numbers using Special or Custom ..Help!!!! | Excel Discussion (Misc queries) | |||
automatic formatting phone numbers in excel | Excel Worksheet Functions | |||
Formatting Phone Numbers with Periods | Excel Discussion (Misc queries) | |||
When merging phone numbers do not keep formatting why? | Excel Worksheet Functions |