Gary please I have been using your phone no format for sometime now and it
works great but all of a sudden its now giving me error. Any help will be
appreciated. I put =phoneNumber(A1) and i get error "#Name" in the cell.

I will really appreciate your help. thanks a lot.

"Gary''s Student" wrote:

Try this UDF:

Function phoneNumber(r As Range) As String
phoneNumber = "("
v = r.Value
L = Len(v)
ncnt = 0
For LL = 1 To L
ch = Mid(v, LL, 1)
If IsNumeric(ch) Then
phoneNumber = phoneNumber & ch
ncnt = ncnt + 1
If ncnt = 3 Then
phoneNumber = phoneNumber & ") "
End If
If ncnt = 6 Then
phoneNumber = phoneNumber & "-"
End If
If ncnt = 10 Then
Exit Function
End If
End If
Next
End Function

if A1 contains:
123.456.7890
=phoneNumber(A1) will return:
(123) 456-7890


If A1 contains:
somejunk123morejunk456almostdone7890back stuff
=phoneNumber(A1) will return:
(123) 456-7890

--
Gary''s Student - gsnu200786


"Yossy" wrote:

please does anyone have any idea how i can format phone no. I want to achieve
this format (xxx) xxx-xxxx. i used this formula
="("&LEFT(G14,3)&")"&MID(G14,4,3)&"-"&RIGHT(G14,4) and because some of my
phone no are in diferent format it didnt give me the right format.
E.G Date I have Format I want Format I get using the formula above
xxx.xxx.xxxx (xxx) xxx-xxxx (xxx) .xx-xxxx
(xxx) xxx-xxxx (xxx) xxx-xxxx ((xx)x) -xxxx

All help appreciated. thanks