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?

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
>
>

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,

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

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

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
>
>

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

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