Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
helpjim
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Abode
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
bgeier
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
helpjim
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
helpjim
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Abode
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
helpjim
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
helpjim
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is it possible to change the default number format in Excel? Andrew Excel Discussion (Misc queries) 5 April 24th 23 09:01 PM
Linking customer number format to a cell Bobak Excel Discussion (Misc queries) 2 August 8th 05 08:09 PM
Global fix for scientific number format? Carol Excel Discussion (Misc queries) 1 April 14th 05 01:48 AM
how to change the h:mm format to number? Bel Excel Worksheet Functions 2 January 10th 05 07:30 PM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM


All times are GMT +1. The time now is 09:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"