I NEED ANOTHER GURU AGAIN FOR THIS ONE!! MACROS!

Posted by Ken on June 21, 2001 6:05 AM

This will be a very specific message that includes all the problems I am trying to fix and it includes examples of all of them. If you could possibly find a MACRO to do all this at the same time, it would be a miracle itself.

Example 1: 203 966-3456 (Just parenthese around the 203)

Example 2: 1203 876-3847 (delete the 1 and put parentheses around the 203)

Example 3: 1 203 564-4364 (delete the 1 and put parentheses around the 203)

Example 4: (612) 698-2590 (leave this alone, if I try to use the formula, all that i get is #VALUE!, I don't that, i just want those numbers to pop up again)

Example 5: (708) 765 5046 Fax 765 0070 tel (get rid of everything after the word "fax" and including the word "fax"; also be sure to leave the front parts alone b/c already has parentheses)

Example 6: 001 802 362 8155 802 362 1987 (get rid of 001 and everything after the second set of "802" and including the secon "802"; place parentheses around the first 802)

Example 7: Fax: 301 469 5871 (get rid of fax and put parenthese around 301)

Example 8: 201 376 3600 x406 (get rid of "x406" and put parenthese around the "201")

Example 9: 570 689 4422-H/822 7482-CH
(708) 765 5046 Fax 765 0070 tel
(and on and on, i think that you get the general idea)

This would be an awesome task to complete and we here at Alpha would be very grateful to you if you could help us solve this problem. If it will take you several days, please notify us of how long you think it will take to do so or if it's just not entirely possible) Thank you

Ken

Posted by David Rainey on June 21, 2001 8:16 AM

This is a pretty good sized program. Is this something that would be done often. How many records do youi have?
Is this something you are looking to hire someone for?

Posted by Ivan F Moala on June 22, 2001 12:25 AM

Ken
Here is a macro that will accomplish this ??
Notes:
1) It only works on the conditions you have supplied.
So if ther are other formats it will not work on them.
Just select the range to change and run it.

Sub Change_TelePhNo()
Dim PhRg As Range
Dim No As Range
Dim L As Integer

Set PhRg = Selection
For Each No In PhRg
L = Len(No)
Select Case L
Case 14 'The correct Format
If Left(No, 1) &LT;&GT; "(" Then
'Correct format has ()
No = "(" & Mid(No, 3, 3) & ")" & Right(No, 9)
End If
Case 12 'Correct number BUT no ()
No = "(" & Mid(No, 1, 3) & ")" & Right(No, 9)
Case 13 'Extra 1st Digit
No = "(" & Mid(No, 2, 3) & ")" & Right(No, 9)
Case 17 '
'Is it an xTension number
If InStr(6, No, "x") = 0 Then
No = "(" & Mid(No, 6, 3) & ")" & Right(No, 9)
Else
'Must be a Fax so....
No = "(" & Mid(No, 1, 3) & ")" & Mid(No, 4, 9)
End If
Case 26
No = "(" & Mid(No, 1, 3) & ")" & Mid(No, 4, 9)
Case 29
No = "(" & Mid(No, 5, 3) & ")" & Mid(No, 8, 9)
Case 31
No = Left(No, 14)
End Select
Next
End Sub

Ivan

Posted by Ivan F Moala on June 22, 2001 2:03 AM

Also note that it would pay to test this 1st on backup data PLUS, your data may have some formating characters in it ?? if the data has been extracted or imported.....so run this modified code; only one line changed...

Sub Change_TelePhNo()
Dim PhRg As Range
Dim No As Range
Dim L As Integer

Set PhRg = Selection
For Each No In PhRg
No = Application.WorksheetFunction.Clean(No)
L = Len(No)
Select Case L
Case 14 'The correct Format
If Left(No, 1) &LT;&GT; "(" Then
'Correct format has ()
No = "(" & Mid(No, 3, 3) & ")" & Right(No, 9)
End If
Case 12 'Correct number BUT no ()
No = "(" & Mid(No, 1, 3) & ")" & Right(No, 9)
Case 13 'Extra 1st Digit
No = "(" & Mid(No, 2, 3) & ")" & Right(No, 9)
Case 17 '
'Is it an xTension number
If InStr(6, No, "x") = 0 Then
No = "(" & Mid(No, 6, 3) & ")" & Right(No, 9)
Else
'Must be a Fax so....
No = "(" & Mid(No, 1, 3) & ")" & Mid(No, 4, 9)
End If
Case 26
No = "(" & Mid(No, 1, 3) & ")" & Mid(No, 4, 9)
Case 29
No = "(" & Mid(No, 5, 3) & ")" & Mid(No, 8, 9)
Case 31
No = Left(No, 14)
End Select
Next
End Sub

Ivan

Posted by ken on July 09, 2001 9:47 AM

Just a one time deal, no need to hire