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

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

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
<(E-Mail Removed)> 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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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

"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
>
>

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
>
>

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" <(E-Mail Removed)>
> wrote in message
> news:(E-Mail Removed)...
> >
> > 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
> >
>
>
>

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
> <(E-Mail Removed)> 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?
>
>

"msf" <(E-Mail Removed)> wrote in message
news:AAC546FB-3477-47D7-9429-(E-Mail Removed)...
>
> 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


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