The student's original code was a user defined function.

You wrapped it in a sub routine.

Can't work that way.

Functions are entered in cells like =PhoneNumber(A1)

Remove the Sub PhoneNumberCreate() and the End Sub

Then enter as above in an adjacent cell and copy down.


Gord Dibben MS Excel MVP


On Wed, 14 May 2008 09:34:00 -0700, Yossy
wrote:

Hi Gary,
Please still get the same error whether i put the last line or not. what am
I doing wrong. Thanks for helping out. really appreciate it.

"Gary''s Student" wrote:

This is my error, just remove the extra line at the end:

End Sub

should not be there.
--
Gary''s Student - gsnu200786


"Yossy" wrote:

Please Gary this is how i used your code - and I still get this error
"Compile Error Expected sub End". Please help me. thanks

Sub PhoneNumberCreate()
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(A1)
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
End Sub

"Gary''s Student" wrote:

Put a phone number in A1 and in B1 enter:

=phoneNumber(A1)
--
Gary''s Student - gsnu200786


"Yossy" wrote:

thanks Gary but the code is not responding at all. Is there anything am
missing???. thanks for your help

"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