Formatting telephone numbers and email addresses in Microsoft Excel version 2003?
Question by Rachel: Formatting telephone numbers and email addresses in Microsoft Excel version 2003?
I have a list of telephone numbers in Microsoft Excel, some in the (###) ### – #### format and others in the ### – ### – #### format. I need a formula or something that will turn them all into the (###) ### – #### format. Also I have a list of email addresses in the same program, I need a way to have the program recognize the “@” symbol and automatically change it into a link. Any suggestions?
Best answer:
Answer by cozmosis
Phone numbers: if your phone numbers are in column A, put this formula in B1 and copy it in B2 to B?. It will make a duplicate column of your phone numbers all formatted as (???) ??? – ????
=IF(LEFT(A1,1)=”(“,A1,”(“&
LEFT(A1,3)&”) “&MID(A1,7,10))
You could then copy column B and Paste Special – Values over column A to replace all the old values with the new ones.
Emails:
Select all the cells with emails
Select InsertHyperlink
Select Link to: E-mail Address (on the left of the dialog)
If the first cell of your selection is D5 then in the E-Mail Address text input box type in =D5
OK
Add your own answer in the comments!
| Print article | This entry was posted by internet4u on October 1, 2010 at 4:04 pm, and is filed under Email Accounts. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
about 1 year ago
for the phone numbers (assuming they’re in Col A starting at A2 — edit the forumula as necessary), put this in B2:
=IF(COUNTIF(A2,”(*”),A2,
“(“&LEFT(A2,3)&”) “&RIGHT(
A2,LEN(A2)-FIND(“-”,A2,1)))
the above assumes that in the numbers formatted ### – ### – ####, there are NO spaces before/after the hyphens, that is, that the total length for those is 12 characters. If that’s not the case, the formula would be slightly different; if it’s *sometimes* the case, the formula will be a little more complicated.
As for the email addresses, usually Excel will recognize these automatically and turn them into links. If not, maybe there are some leading/trailing spaces; regardless, you can use the TRIM function just in case, along with the hyperlink function. If your first email address was in D2, you’d put this in E2:
=HYPERLINK(TRIM(D2),D2)
as opposed to simply:
=HYPERLINK(D2,D2)
or, if you wanted them to appear without the domain name (i.e. just show everything to the left of the @), but still maintain the same link underneath, you would write:
=HYPERLINK(TRIM(D2),LEFT(
TRIM(D2),FIND(“@”,TRIM(D2),1)-1))
or, better yet, if you another column with the person’s actual name (for instance, name in C2), and you want their name to appear as a hyperlink to their email:
=HYPERLINK(TRIM(D2),C2)
about 1 year ago
In a clear space say column AA put in row 1
=IF(LEFT(A1,1)=”(“,A1,”(“&
LEFT(A1,3)&”) “&RIGHT(A1,8))
and copy down as many rows as in column A..
Copy the values in AA and Paste Special Values in A.
If you wish you can select the whole of column A then from the menu select Format Cells Number Special Phone Numbers and all new entries can be entered as 10 digits and Excel will add the brackets and the dash for you.when you hit Enter.
Re-type the address, making sure it is correct when you press enter excel will automatically change it to a link.