Phone book formatting

[Yossy]

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

 

[Gary''s Student]

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

 

[JLGWhiz]

If your system is set for U.S. (English) then you can use
Form>Cells>Special>Telephone and format the entire range.

 

[Yossy]

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

 

[Gary''s Student]

Put a phone number in A1 and in B1 enter:

=phoneNumber(A1)

 

[Yossy]

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]

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

End Sub

should not be there.

 

[Yossy]

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.

 

[Gord Dibben]

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

 

[Yossy]

Got ya!!. It took me few days to finally understand what you guys mean.
thanks for helping out. It works now that I figured it out. Thanks

 

[Gord Dibben]

Good to hear.

Gord

Got ya!!. It took me few days to finally understand what you guys mean.
thanks for helping out. It works now that I figured it out. Thanks

 

[Yossy]

Hey Guys,
I need to add the extension. Please help.

(000) 000-0000 x000

Extension no could be between 2 to 5

Will appreciate your help. Thanks so much

 

[Gord Dibben]

How would one determine which extension went with which phone number?

=PhoneNumber(a1) & " ext " & b1 returns (123) 456-7890 ext 1234

Assuming B1 holds 1234


Gord

 

[Yossy]

Gary please help me on this one. There are some numbers that have extensions
in them. I want the result to come out this way (000) 000-0000 x000 .
Your code gives me the result as this (xxx) xxx-xxxx which is good but
excludes the extension.

Extensions however could be between 2 to 5 character. Thanks so much for
your help. really appreciated.

 

[Yossy]

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.