Phone Number Formatting

[LeggoDave]

Hi everyone! I'm currently creating a VBA that formats phone number. All numbers must follow a standard format.
The scenario goes like this:
In the user's selection, or highlighted part/selected part, all phone number formats will be "three numbers" "-" "three numbers" "-" "four numbers".
*However this 'format' should only apply to US phone numbers, or telephone numbers that starts with +1
The VBA code should also be able to translate letters into numbers(phone letters) such as:
ABC = 2, DEF = 3, GHI = 4, JKL = 5, MNO = 6, PQRS = 7, TUV = 8, WXYZ = 9
For example, in a selection,
+1 (900) 800 8792
9118992343
281.928.9032
800 500 100
(+1) (839) 737 7289
ABC-DEF-ABC
900-900-9000
900-AAA-8ABE


Must be translated into the same location,
900-800-8792
911-899-2343
281-928-9032
800-500-100
839-737-7289
222-333-222
900-900-9000
900-222-8223


The code should be dynamic though. As you can see in the examples, letters are translated into numbers. All special characters are omitted, and the area code is removed. I'm still learning so I hope anyone would like to help me with this one. Thanks in advance!

 

[LeggoDave]

Hi Rick! So as I was trying it to my files, I found out that I left an uncovered spot. I forgot to include in my post that the phone numbers should only be in the US! (+1 country code) If the phone number starts with different country code, such as (+44), (+63) and the like, other than +1, the cell should be highlighted. However, if there is no country included, the regular formatting which is ###-###-#### should be followed.
For further reference about this, here is my example:
(+1) 900 (800) 8292
+44 99 008 9283
900.292.3938
(02) 903 838 9282


should be like this,
900-800-8292
+44 99 008 9283 (***let's just follow the highlight rule for insufficient values)
900-292-3938
(02) 903 838 9282(***)

I was trying to follow my logic and execute the code but I can't express it through code. I was thinking that the count values should start from right, and then accept 10 values, if there are remaining values, there should be a validation if the remaining is == 1. If not, then the cell must be highlighted and no formatting should be done. But howwwwww

 

[Rick Rothstein]

Hi Rick! So as I was trying it to my files, I found out that I left an uncovered spot. I forgot to include in my post that the phone numbers should only be in the US! (+1 country code) If the phone number starts with different country code, such as (+44), (+63) and the like, other than +1, the cell should be highlighted. However, if there is no country included, the regular formatting which is ###-###-#### should be followed.

I think the following code will do what you are asking for here...

[table="width: 500"]
[tr]
	[td]Sub PhoneNumber()
  Dim X As Long, Original As String, CellText As String, Cell As Range
  For Each Cell In Selection
    Original = Cell.Value
    CellText = UCase(Cell.Value)
    For X = 1 To Len(CellText)
      If Mid(CellText, X, 1) Like "*[!0-9A-Z]*" Then Mid(CellText, X) = " "
    Next
    CellText = Replace(CellText, " ", "")
    If Len(CellText) = 10 Or CellText Like "1??????????" Then
      For X = 1 To Len(CellText)
        If Mid(CellText, X, 1) Like "[A-Z]" Then Mid(CellText, X) = 2 + Int((InStr("ABC DEF GHI JKL MNO PQRSTUV WXYZ", Mid(CellText, X, 1)) - 1) / 4)
      Next
      Cell.Value = Format(CellText, "000-000-0000")
    Else
      Cell.Value = Original
      Cell.Interior.Color = vbYellow
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[LeggoDave]

Thanks!!!

 

[LeggoDave]

thanks for responding, Rick! As I was executing your code, I found out that there are some events that I think are unexpected.
with your new code,
+1012-254-5878
+1.900.900.9000
+1 AAA BBB CCCC

are not translated. Even testing thoroughly, with a phone number which is (+10) but is incomplete, or incompatible with the logic, it becomes wrong
like on this one (+10) 545 878 545

If this is a little too much, it's okay You're a big help Rick thank you

 

[Rick Rothstein]

thanks for responding, Rick! As I was executing your code, I found out that there are some events that I think are unexpected.
with your new code,
+1012-254-5878
+1.900.900.9000
+1 AAA BBB CCCC

are not translated. Even testing thoroughly, with a phone number which is (+10) but is incomplete, or incompatible with the logic, it becomes wrong
like on this one (+10) 545 878 545

Does this macro work correctly?

[table="width: 500"]
[tr]
	[td]Sub PhoneNumber()
  Dim X As Long, Original As String, CellText As String, Cell As Range
  For Each Cell In Selection
    Original = Cell.Value
    CellText = UCase(Cell.Value)
    For X = 1 To Len(CellText)
      If Mid(CellText, X, 1) Like "*[!0-9A-Z]*" Then Mid(CellText, X) = " "
    Next
    CellText = Replace(CellText, " ", "")
    If Len(CellText) = 10 Or CellText Like "1??????????" Then
      CellText = Right(CellText, 10)
      For X = 1 To Len(CellText)
        If Mid(CellText, X, 1) Like "[A-Z]" Then Mid(CellText, X) = 2 + Int((InStr("ABC DEF GHI JKL MNO PQRSTUV WXYZ", Mid(CellText, X, 1)) - 1) / 4)
      Next
      Cell.Value = Format(CellText, "000-000-0000")
    Else
      Cell.Value = Original
      Cell.Interior.Color = vbYellow
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[LeggoDave]

Hi, Rick! It works with (Country code) + (10 phone numbers)!!! May you please discuss this part:

If Len(CellText) = 10 Or CellText Like "1??????????" Then

Thanks!!

 

[Rick Rothstein]

May you please discuss this part:

If Len(CellText) = 10 Or CellText Like "1??????????" Then

Thanks!!

A number is valid (by your standards) if it contains exactly 10 characters (letters to be converted to numbers later), which was the first test, or if it contained exactly 11 characters where the first character is a one, which is the second test. About that second test... the Like operator can use wildcards to make a comparison between the text you are testing and a generalized pattern. In this case, the pattern is a 1 followed by 10 characters (the question mark is a wildcard that stands in for any single character).

 

[LeggoDave]

Rick I have some questions regarding your code:


1. What happens at this part of the code?


"*[!0-9A-Z]*" Then Mid(CellText, X) = " "


What does the "*" and the "!" means? And am I right for supposing that characters other than 0-9A-Z are translated into " "? Why is the argument "Mid(CellText, X)" ?




2. And would you please explain this further?


Int((InStr("ABC DEF GHI JKL MNO PQRSTUV WXYZ", Mid(CellText, X, 1)) - 1) / 4)


Why is "If Mid(CellText, X, 1) Like "[A-Z]" Then Mid(CellText, X)" equal to "2" ?


And could you demonstrate/illustrate a simple manual simulation? Thank you, Rick!!!! As in!!!