software

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: www.excelforum.com/member.php...oamp;userid=30978
View this thread: www.excelforum.com/showthread...hreadid=543124Hello 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 gt; Numbers gt;
Special. How is that locked. You would probably have more sucsess with
going to Format gt; Numbers gt; Custom and in the area where you can type put
something like (000)000-0000. Hopefully that option isn't locked also.

quot;helpjimquot; wrote:

gt;
gt; List of phone numbers need to be re-formatted. Am unable to remove/clear
gt; format. Number is 123/4567890. Unable going to format, special, phone
gt; number to change. Locked?
gt;
gt;
gt; --
gt; helpjim
gt; ------------------------------------------------------------------------
gt; helpjim's Profile: www.excelforum.com/member.php...oamp;userid=30978
gt; View this thread: www.excelforum.com/showthread...hreadid=543124
gt;
gt;


The cells are probably not locked.
The issue is the quot;/quot; that is a non-numeric character. Excel does not
know what to do with it so it makes the entire cell quot;Textquot;.
Using format -gt; special -gt; will not work because Excel does not
recognize the quot;/quot; character as a valid separator.

To make the change you will have to use some type of formula such as
=quot;(quot; amp; LEFT(D2,3) amp; quot;) quot; amp; MID(D2,5,3) amp; quot;-quot; amp; 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) lt;gt; quot;quot;
Cells(dblRowCounter, 1) = quot;(quot; amp; Left(Cells(dblRowCounter, 1),
3) amp; quot;) quot; amp; _
Mid(Cells(dblRowCounter, 1), 5, 3) amp; quot;-quot; amp; _
Right(Cells(dblRowCounter, 1), 4)
dblRowCounter = dblRowCounter 1
Loop
End Sub

this will loop through all of the cells in column quot;Aquot; and make the
change for you.--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543124
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: www.excelforum.com/member.php...oamp;userid=30978
View this thread: www.excelforum.com/showthread...hreadid=543124
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: www.excelforum.com/member.php...oamp;userid=30978
View this thread: www.excelforum.com/showthread...hreadid=543124No 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

=quot;(quot; amp; LEFT(D2,3) amp; quot;) quot; amp; MID(D2,5,3) amp; quot;-quot; amp; 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 gt; Paste Special. Click on Values then OK.
That should do it. Hopefully this solves your problem.

quot;helpjimquot; wrote:

gt;
gt; Abode,
gt;
gt; Thanks for your reply. I did go back and try your suggestion; however
gt; it did not work. I had tried another similiar which did not work
gt; either.
gt;
gt; helpjim
gt;
gt;
gt; --
gt; helpjim
gt; ------------------------------------------------------------------------
gt; helpjim's Profile: www.excelforum.com/member.php...oamp;userid=30978
gt; View this thread: www.excelforum.com/showthread...hreadid=543124
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30978
View this thread: www.excelforum.com/showthread...hreadid=543124
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: www.excelforum.com/member.php...oamp;userid=30978
View this thread: www.excelforum.com/showthread...hreadid=543124