Changing Phone Number Formats

[Tha BeatMaker]

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?

 

[Anne Troy]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Gord Dibben]

See more suggestions in the other news group you multi-posted to.


Gord Dibben Excel MVP

 

[Philippe L. Balmanno]

"Tha BeatMaker" <[email protected]>
wrote in message

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?

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

 

[Guest]

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?

 

[Guest]

How do I chane the format of phone numbers in Outlook 2003 contacts? Simple
terms, please

 

[Guest]

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.

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" <[email protected]>
wrote in message

 

[Guest]

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.

 

[Alan]

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


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+