#1
|
|||
|
|||
Converting Phone Numbers Question
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!!! |
#2
|
||||
|
||||
Does every number contain 10 digits?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
If so
Code:
=LEFT(A1,3)&"-"&MID(A1,4,3)&"-"&right(A1,4)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
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" ? |
#5
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Converting Phone Numbers Question
Would another way out of this be to set up a custom number format. Set the format as 000-000-0000.
Regards Tony |
#7
|
||||
|
||||
Of course, but the OP stated that he did not want this solution ( see his post)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
|||
|
|||
That'll teach me to post in a hurry.
Regards Tony |
#9
|
||||
|
||||
Np, happens to me more than often enough
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create Custom Labels for Phone numbers | Tonistep | Outlook | 0 | 03-26-2012 02:57 PM |
Can delete on phone but still dnlds to netbook | peterlckwd | Outlook | 0 | 03-14-2012 04:29 AM |
Changing phone categories in Outlook Contact | cwksr | Outlook | 1 | 05-06-2011 08:44 AM |
Phone number formatting | Mark Micallef | Outlook | 1 | 08-04-2010 02:38 PM |
Phone call through TAPI driver | drdebate | Outlook | 0 | 04-09-2007 01:46 PM |