# Thread: Excel Macro Problem (Excel 2003)

1. ## 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. ## 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
•