| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Phone Number Formats
Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
You need to get rid of the extra characters 'cause it's likely they're not
"formats", but actual characters typed in. Just select the column. Using find and replace (Ctrl+H), enter the following into the Find What box, one at a time, and hit replace all. Don't put anything into the Replace with box. I follow we a description in parentheses so you can be sure what the character is. Do NOT type the descriptions into the Find What box, ONLY the character: .. (period) ( (open paren) ) (close paren) - (hypen) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Tha BeatMaker" wrote in message news:Tha.BeatMaker.1ribif_1120248309.915@excelforu m-nospam.com... Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
How do I cange the format for phones numbers in contacts in Outlook 2003. It
used to automatically format to (xxx) xxx-xxxx but now formats as xxxxxxxxxx. I am a non techie so simple terms would be appreciated. Thank you. "Anne Troy" wrote: You need to get rid of the extra characters 'cause it's likely they're not "formats", but actual characters typed in. Just select the column. Using find and replace (Ctrl+H), enter the following into the Find What box, one at a time, and hit replace all. Don't put anything into the Replace with box. I follow we a description in parentheses so you can be sure what the character is. Do NOT type the descriptions into the Find What box, ONLY the character: .. (period) ( (open paren) ) (close paren) - (hypen) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Tha BeatMaker" wrote in message news:Tha.BeatMaker.1ribif_1120248309.915@excelforu m-nospam.com... Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
See more suggestions in the other news group you multi-posted to.
Gord Dibben Excel MVP On Fri, 1 Jul 2005 14:13:30 -0500, Tha BeatMaker wrote: Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
How do I cange the format for phones numbers in contacts in Outlook 2003. It
used to automatically format to (xxx) xxx-xxxx but now formats as xxxxxxxxxx. I am a non techie so simple terms would be appreciated. Thank you. "Gord Dibben" wrote: See more suggestions in the other news group you multi-posted to. Gord Dibben Excel MVP On Fri, 1 Jul 2005 14:13:30 -0500, Tha BeatMaker wrote: Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
"msf" wrote in message
... How do I cange the format for phones numbers in contacts in Outlook 2003. It used to automatically format to (xxx) xxx-xxxx but now formats as xxxxxxxxxx. I am a non techie so simple terms would be appreciated. Thank you. Hi msf, I use the following sub, but there is probably a better way to do it. Obviously you would have to change the search terms to suit, and if you have contacts from far and wide as I do, then perhaps just use the code to fix 90% of them and do the rest manually. I cannot be bothered to write the code for ones outside of NZ and Aussie, but you can amend to suit hopefully. HTH, Alan. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Sub FindReplacePhoneNumbers() Const MaxCount = 11 Dim ConvertItem As ContactItem Dim MyFind(MaxCount) As String Dim MyReplace(MaxCount) As String Set SelectedItems = ActiveExplorer.Selection MyFind(1) = "(09)" MyReplace(1) = "+64 - 9 -" MyFind(2) = "+61 2 " MyReplace(2) = "+61 - 2 - " MyFind(3) = "+61 3 " MyReplace(3) = "+61 - 3 - " MyFind(4) = "025 " MyReplace(4) = "+64 - 25 - " MyFind(5) = "021 " MyReplace(5) = "+64 - 21 - " MyFind(6) = "(06)" MyReplace(6) = "+64 - 6 -" MyFind(7) = " 06" MyReplace(7) = "+64 - 6 -" MyFind(8) = "+61 02 " MyReplace(8) = "+61 - 2 - " MyFind(9) = "+61 (0412) " MyReplace(9) = "+61 - 412 - " MyFind(10) = "(03)" MyReplace(10) = "+64 - 3 -" MyFind(11) = "(027)" MyReplace(11) = "+64 - 27 -" For Counter = 1 To MaxCount For Each ConvertItem In SelectedItems ConvertItem.OtherTelephoneNumber = Replace(ConvertItem.OtherTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.HomeTelephoneNumber = Replace(ConvertItem.HomeTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.BusinessTelephoneNumber = Replace(ConvertItem.BusinessTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.MobileTelephoneNumber = Replace(ConvertItem.MobileTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.CompanyMainTelephoneNumber = Replace(ConvertItem.CompanyMainTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.Business2TelephoneNumber = Replace(ConvertItem.Business2TelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.TTYTDDTelephoneNumber = Replace(ConvertItem.TTYTDDTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.CarTelephoneNumber = Replace(ConvertItem.CarTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.RadioTelephoneNumber = Replace(ConvertItem.RadioTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.PrimaryTelephoneNumber = Replace(ConvertItem.PrimaryTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.Home2TelephoneNumber = Replace(ConvertItem.Home2TelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.AssistantTelephoneNumber = Replace(ConvertItem.AssistantTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.CallbackTelephoneNumber = Replace(ConvertItem.CallbackTelephoneNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.HomeFaxNumber = Replace(ConvertItem.HomeFaxNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.BusinessFaxNumber = Replace(ConvertItem.BusinessFaxNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.OtherFaxNumber = Replace(ConvertItem.OtherFaxNumber, MyFind(Counter), MyReplace(Counter)) ConvertItem.Save Next Next End Sub +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
"Tha BeatMaker"
wrote in message news:Tha.BeatMaker.1ribif_1120248309.915@excelforu m-nospam.com... Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 I simply chose column "A" and recorded a new macro, for This Workbook - not any other workbook type, {Called it something - RSPCHR} that replaced all "each character one at a time" with nothing and, in the macro I changed the format as some phone numbers came out like 3.37E+09. This recorded the code and can be viewed in edit macro. Here is what came out. Sub RSPCHR() ' ' RSPCHR Macro ' Macro recorded 7/1/2005 by Philippe L. Balmanno ' ' Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.NumberFormat = "0.00" Selection.NumberFormat = "General" Selection.NumberFormat = "@" End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
"Tha BeatMaker" wrote: Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Phone Number Formats
How do I chane the format of phone numbers in Outlook 2003 contacts? Simple
terms, please "Tha BeatMaker" wrote: Consider this example: A1 - 3365551111 A2 - 336-555-2222 A3 - (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php...o&userid=23998 View this thread: http://www.excelforum.com/showthread...hreadid=383978 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Phone Number Formats | Excel Discussion (Misc queries) | |||
| Changing the format of a phone number | Excel Worksheet Functions | |||
| Changing Phone number format | Excel Worksheet Functions | |||
| Phone Number Formats | Excel Discussion (Misc queries) | |||
| Phone number formats | Excel Discussion (Misc queries) | |||
![Tha BeatMaker[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
