Custom Format for Phone Nmuber with Letters

[dcoledc]

Does anyone know how to code a custom format for a phone number with letters in it?

I am currently using this code for phone numbers in general.

With Selection
    .NumberFormat = "[<=9999999]###-####;(###) ###-####"
End With

It the user enters a number, such as 770789HELP, it will not format. I want this to format like so: (770) 789-HELP

Any thoughts?

 

[machopicho]

i didn't use number format to obtaine the result that you want, but considering as works this string:

.NumberFormat = "[<=9999999]###-####;(###) ###-####"

i wrote this macro:

Sub FormatNumberPhone()
ls = Len(Range("A1"))
For i = 1 To ls
If IsNumeric(Mid(Range("A1"), i, 1)) Then
num = num & Mid(Range("A1"), i, 1)
End If
Next
lnn = Len(num)
lt = ls - lnn
numstr = "(" & Left(num, lnn - 7) & ")" & " " & Mid(num, lnn - 6, 3) & "-" & Right(num, 4)
Range("A1") = numstr & Right(Range("A1"), lt)
End Sub

Try it with string in A1!

 

[dcoledc]

Wow, that's some serious coding. Unfortunately, my version of excel seems to have problems with Left, Right, etc. coding. I plan on upgrading soon. Thanks for giving it a shot, perhaps someone else will be able to use it. Someone whose Excel works properly.

I had the same problem with coding someone provided to check to see if a file is open.

Take Care

P.S. I will save this for when I get a new computer and update in Excel