Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Excel Macro Problem (Excel 2003)

    HI,


    I M very confused about a macro..

    I am working in a BPO and we guys are facing a big problem when we look at our numbers.

    Now, my problem is ... I have a Column that contains a thousands of numbers in the sample format given below:


    +5628734 3498 30549
    0 8 438 56609
    3232943 456576
    4575454 54554
    43434354545
    '+45434345
    .
    .
    .
    & So on


    I want a macro that can arrange these numbers in a format of


    +56 28 734 349830549
    +08 43 856 609
    +32 32 943 456576
    +45 75 454 54554
    +43 43 435 4545
    +45 43 434 5


    That means .... +Countrycode("+" and 2 digits) after that, space, then State code(2 digits), space, then Area code(3 digits), space, then remaining numbers...



    I will be very thankful to you if u'll help me out in my problem.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro Problem (Excel 2003)

    Try this; change the constants strCol and lngFirst as needed (they indicate the column with the phone numbers and the starting row):
    <code>
    Sub FormatPhoneNumbers()
    Const strCol = "A"
    Const lngFirst = 1
    Dim r As Long
    Dim m As Long
    Dim strPhone As String
    m = Range(strCol & Cells.Rows.Count).End(xlUp).Row
    For r = lngFirst To m
    strPhone = Range(strCol & r)
    strPhone = Replace(strPhone, " ", "")
    strPhone = Replace(strPhone, "+", "")
    strPhone = "+" & Left(strPhone, 2) & " " & _
    Mid(strPhone, 3, 2) & " " & Mid(strPhone, 5, 3) & _
    " " & Mid(strPhone, 8)
    Range(strCol & r) = strPhone
    Next r
    End Sub</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •