Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
If your system is set for U.S. (English) then you can use
FormCellsSpecialTelephone and format the entire range. "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
Good to hear.
Gord On Fri, 16 May 2008 12:51:15 -0700, Yossy wrote: 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" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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" wrote: Good to hear. Gord On Fri, 16 May 2008 12:51:15 -0700, Yossy wrote: 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" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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 On Tue, 20 May 2008 13:17:13 -0700, Yossy wrote: 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" wrote: Good to hear. Gord On Fri, 16 May 2008 12:51:15 -0700, Yossy wrote: 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" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone book formatting
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. "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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Phone Number formatting
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Phone book formatting | Excel Programming | |||
Formatting phone numbers | Excel Discussion (Misc queries) | |||
Phone Formatting | Excel Discussion (Misc queries) | |||
Phone book problem | Excel Worksheet Functions | |||
Phone Number Formatting | Excel Discussion (Misc queries) |