IF statment with cell formating

[baggins22]

Hey everybody,

i have a list of numbers, some foreign and some domestic

i want to be able to enter a string of numbers and have it format the cell accordingly.

Ex.

if i enter 5555555555 it will look like (555)555-5555
but if i enter a foreign number : 912255555555 i want it to look like 91 (22) 5555-5555

Im not sure if this is possible, if necessary i will add another column with a "d" (for domestic) or a "I"( for international). In this case would it be possible to do a IF statement?

Ex. If "D" use (555)555-5555 Format
IF "I" use 91 (22) 5555-5555 Format

[mdmackillop]

Place the following code in the worksheet module. It will format numbers placed in Column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Select Case Len(Target)
Case 10
Target.NumberFormat = "(000)000-0000"
Case 12
Target.NumberFormat = "00(00)0000-0000"
Case Else
Target.NumberFormat = "0"
End Select
Application.EnableEvents = True
End If
End Sub

BTW, you have the wrong length of numbers for UK, which would be 00(44)1234-123456

[baggins22]

Thanks,

That works great.

For some reson when i select mutilple rows to delect it gives a me error - "type mismatch, run-time error 13"

Any ideas?

Thanks

[mdmackillop]

The code is designed to work on new entries as per your post. Do you need to format an existing list?

[baggins22]

I might need to change the phone numbers at some point

Thanks

[baggins22]

How would i change that formula to allow me to edit phone numbers

Thanks

[mdmackillop]

This will run the code on a selection
Sub Phone()
Dim cel as Range
For Each cel In Selection
Select Case Len(cel)
Case 10
cel.NumberFormat = "(000)000-0000"
Case 12
cel.NumberFormat = "00(00)0000-0000"
Case Else
cel.NumberFormat = "0"
End Select
Next
End Sub