Format change of phone number

[helpjim]

List of phone numbers need to be re-formatted. Am unable to remove/clea
format. Number is 123/4567890. Unable going to format, special, phon
number to change. Locked

 

[Guest]

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.

 

[bgeier]

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.

 

[helpjim]

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]

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

 

[Guest]

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]

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]

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