Converting Phone Numbers Question

[certified_techy]

Good Morning,



I need assistance with converting phone numbers in an Excel spreadsheet.

Currently, the numbers all show up as "1234567890", but I need them to show up as "123-456-7890". I know that there's an option to convert the text to look like a phone number, but that's not what I need. Yes, it changes the appearance of the number in the cell, but I need the number to be changed in the cell when you click on it.

For example;

I have 1234567890 in cell 1A. In the function bar, it shows as 1234567890.

I can change the appearance to be (123) 456 - 7890, which is ok, however it just changes the appearance.

I need it to change it in the cell from 1234567890 to 123-456-7890.

I've tried format painter, find and replace, the only thing that I can think of to do at this point is go line by line and change it. My sheet has about 1000 numbers in it.

Help!!!

[Pecoflyer]

Does every number contain 10 digits?

[Pecoflyer]

If so

=LEFT(A1,3)&"-"&MID(A1,4,3)&"-"&right(A1,4)

[certified_techy]

So, how would that look with a phone number in it? And how could I do a "find and replace" and get that added to the front (which is where I'm assuming it's supposed to go)?

Would it look like "=LEFT(A1,3)&"-"&MID(A1,4,3)&"-"&RIGHT(A1,4)1234567890" ?

[Pecoflyer]

You will have to enter this formula in another cell ( B1, say) and pull down as needed
When you have the results - Copy Paste Special - Values to the place you want

[awstyle]

Would another way out of this be to set up a custom number format. Set the format as 000-000-0000.

Regards

Tony

[Pecoflyer]

Of course, but the OP stated that he did not want this solution ( see his post)

[awstyle]

That'll teach me to post in a hurry.

Regards

Tony

[Pecoflyer]

Np, happens to me more than often enough