Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
List of phone numbers need to be re-formatted. Am unable to remove/clear format. Number is 123/4567890. Unable going to format, special, phone number to change. Locked? -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
Hello Jim. Sorry but it is very hard to understand what you are saying.
From what I think I got, you are trying to format the number from 123/4567890 to something else. Probably (123)456-7890. And also I think you said you are trying to change the number format by going to the Format Numbers Special. How is that locked. You would probably have more sucsess with going to Format Numbers Custom and in the area where you can type put something like (000)000-0000. Hopefully that option isn't locked also. "helpjim" wrote: List of phone numbers need to be re-formatted. Am unable to remove/clear format. Number is 123/4567890. Unable going to format, special, phone number to change. Locked? -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
The cells are probably not locked. The issue is the "/" that is a non-numeric character. Excel does not know what to do with it so it makes the entire cell "Text". Using format - special - will not work because Excel does not recognize the "/" character as a valid separator. To make the change you will have to use some type of formula such as ="(" & LEFT(D2,3) & ") " & MID(D2,5,3) & "-" & RIGHT(D2,4), but to do this you will have another column with the phone number, that will have to be cut and pasted back to where your original column. Or you could use a VBA macro such as Sub Reformat() Dim dblRowCounter As Double dblRowCounter = 1 Do While Cells(dblRowCounter, 1) < "" Cells(dblRowCounter, 1) = "(" & Left(Cells(dblRowCounter, 1), 3) & ") " & _ Mid(Cells(dblRowCounter, 1), 5, 3) & "-" & _ Right(Cells(dblRowCounter, 1), 4) dblRowCounter = dblRowCounter + 1 Loop End Sub this will loop through all of the cells in column "A" and make the change for you. -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
bgeier, Thanks for the reply. However this is over my head with the formula. I am basic with formulas and my list of numbers is about 3500. If you could write the formula so that I can copy and paste, I can place in formula bar. helpjim -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
Abode, Thanks for your reply. I did go back and try your suggestion; however it did not work. I had tried another similiar which did not work either. helpjim -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
No problem. I totally forgot about the / in there. bgeiers suggestion
should work. All you will have to do is copy the formula below into an empty column. then change where the three 'D2's are. Then using the fill handle drag this forumla down. Here is the forumla ="(" & LEFT(D2,3) & ") " & MID(D2,5,3) & "-" & RIGHT(D2,4) When you do that you should see in that column all of the currect phone numbers. Select all of those and copy them. Then over where your original phone numbers are right-click Paste Special. Click on Values then OK. That should do it. Hopefully this solves your problem. "helpjim" wrote: Abode, Thanks for your reply. I did go back and try your suggestion; however it did not work. I had tried another similiar which did not work either. helpjim -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
Abode, Thank you, thank you for your followup. I have been able to make my change according to the formula. What can I say? Thanks again Helpjim :) -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format change of phone number
bgeier, Thank you very much for you going the extra mile with this issue. It is a great feeling to know that there is qualified assistance in these times of need from people like you and Abode. Helpjim :) -- helpjim ------------------------------------------------------------------------ helpjim's Profile: http://www.excelforum.com/member.php...o&userid=30978 View this thread: http://www.excelforum.com/showthread...hreadid=543124 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to change the default number format in Excel? | Excel Discussion (Misc queries) | |||
Linking customer number format to a cell | Excel Discussion (Misc queries) | |||
Global fix for scientific number format? | Excel Discussion (Misc queries) | |||
how to change the h:mm format to number? | Excel Worksheet Functions | |||
How do i change numbers in text format to number format? | New Users to Excel |