 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007reformat phone no list - macro?
Have HUNDREDS of phone nos in a column, display example 01456 123456 Some bright spark wants them re-formatted so the exchange is in brackets, eg (01456) 123456. Can anyone suggest a macro for this task, please? Help would be much appreciated! The exchange code can be from 4 to 7 digits in length, although it is my experience that a space is often inserted after the first 5 characters, as in your example. If your data is like this, then the brackets won't necessarily encompass the exchange code. Nevertheless, you could do what you want by this formula: ="("&LEFT(A2,FIND(" ",A2)-1)&")"&RIGHT(A2,LEN(A2)-FIND(" ",A2)+1) which will look for a space to separate the number into two parts, assuming it is in cell A2. Hope this helps. Pete > Have HUNDREDS of phone nos in a column, display example 01456 123456 > Some bright spark wants them re-formatted so the exchange is in brackets, > eg (01456) 123456. > Can anyone suggest a macro for this task, please? Help would be much > appreciated! Thanks, Pete, this does the job. Re "exchanges" - you are correct in your reservation. London (0207 or 0208) and Nottingham (0115) are notable 4 digit examples !! Fortunately local (5 digit) exchange numbers are my only concern. Thanks again > The exchange code can be from 4 to 7 digits in length, although it is > my experience that a space is often inserted after the first 5 [quoted text clipped - 17 lines] >> Can anyone suggest a macro for this task, please? Help would be much >> appreciated! Thanks for the feedback - glad it worked for you. Pete > Thanks, Pete, this does the job. > [quoted text clipped - 24 lines] > >> Can anyone suggest a macro for this task, please? Help would be much > >> appreciated!- Hide quoted text -- Show quoted text - If the value in the cell is really 01456 123456 (with that space) Then maybe you could use a formula like: ="("&SUBSTITUTE(TRIM(A1)," ",") ") in an adjacent cell (and drag down) If it works, select that column edit|copy edit|paste special|values over the original column and delete that helper column > Have HUNDREDS of phone nos in a column, display example 01456 123456 > Some bright spark wants them re-formatted so the exchange is in brackets, > eg (01456) 123456. > Can anyone suggest a macro for this task, please? Help would be much > appreciated!  SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.