Changing Phone Number Formats

  • Thread starter Thread starter Tha BeatMaker
  • Start date Start date
T

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?
 
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]...
 
See more suggestions in the other news group you multi-posted to.


Gord Dibben Excel MVP
 
"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?
Click to expand...

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
 
Tha BeatMaker said:
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?
Click to expand...
 
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 said:
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
Click to expand...
 
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.
 
msf said:
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.
Click to expand...

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


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
 
Back
Top