Phone Number Formatting

[ashok0141]

Hi Team,

need help to apply a format on phone number as per the below condition

First 3 digits in brackets, then a space then next 3 digits then hyphen"-" then all remaining digits only.

for E.g
data available like "(97)1-718- 11-71", data require in format like: (971) 718-1171


I have also attached the a excel sheet with similar data.

regards,
Ashok

[jindon]

B2

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ",""),"(000) 000-0000")

then copy down.

[ashok0141]

Thanks Jindon

the above formula is giving output "(21808) 111-1722" if value in cell is 218081-111722


Regards,
Ashok

[jindon]

How do you want it?

[ashok0141]

required data must be in format "(218) 081-111722

formula provided by you is working perfectly with 10 digits of numbers, but it give invalid format if number are less then 10 or more then 10 digits.

Regards,
Ashok

[meabrams]

Try

Not sure what number is the extra number on that one or how you want that to display

Macro

Function ExtractPhone(s As String) As Variant
    s = Replace(s, "(", "")
    s = Replace(s, ")", "")
    s = Replace(s, "-", "")
    ExtractPhone = CCur(Val(s))
End Function

Formula

=TEXT(ExtractPhone(A16),"(000) 000-0000")

[ashok0141]

@meabrams, the macro is also working fine but with 10 digits only, it adds "0" if digits are less then 10 and gives error if digits are more then 10.

[jindon]

B2
=GetPhone(A2)

Function GetPhone(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        txt = .Replace(txt, "")
        .Pattern = "^(\d{3})(\d{3})(\d+)$"
        GetPhone = .Replace(txt, "($1) $2-$3")
    End With
End Function

[ashok0141]

Thanks Jindon,
Thanks meabrams

Finally the "GetPhone" is working with all the numbers.

regards,
Ashok


Regards,
Ashok