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

Please Login or Register  to view this content.

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?

[meabrams]

Try

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

Macro

Please Login or Register  to view this content.

Formula

Please Login or Register  to view this content.

[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

[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)

Please Login or Register  to view this content.

[ashok0141]

Thanks Jindon,
Thanks meabrams

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

regards,
Ashok


Regards,
Ashok