code needed, please

[Guest]

I have a list going down column A. The company name is in
the same cell as the telephone number. Is there a code
that will sperate the telephone numbers and put them in
column B driectly beside the company associated with it
(leaving the company in Column A)
example:

A & F Equipment Sales 519-252-5663
A & S Win-Win Trading Inc 416-960-0980
A-1 Auto Parts Ltd 800-263-9323
A-1 Auto Parts Ltd 905-549-4115
AC Delco Auto Parts Plus 705-267-1655
Acklands Limited 905-877-0143
Acklands-Grainger Inc 807-274-7266
Advanced Spraying Equipment 905-624-7400
Agablock Auto Parts Manufacturing Inc 416-759-3338
AJCO Automotive 613-969-4455
AJCO Automotive 800-668-4455
AKN Automotive Ltd 705-726-2333
Akzo Nobel Coatings Inc 416-438-4489
Allstar Autobody Supply Ltd 905-294-5888
Antica Auto Body Ltd 905-771-3770
APC Best Choice Inc 905-799-2729
APC Choice Inc 905-639-8979
Atlas Auto Parts 705-254-6618
Auto & Marine 93 705-526-5073

 

[Debra Dalgleish]

On a backup copy of the original data --
Insert 2 columns to the right of column A
In cell B1, enter: =LEFT(A1,LEN(A1)-13)
In cell C1, enter: =RIGHT(A1,12)
Copy the formulas down to the last row of data
Select columns B:C, and choose Edit>Copy
With the columns still selected, choosed Edit>Paste Special, Values
Delete column A.

 

[Guest]

worked great, thanks so much

-----Original Message-----
On a backup copy of the original data --
Insert 2 columns to the right of column A
In cell B1, enter: =LEFT(A1,LEN(A1)-13)
In cell C1, enter: =RIGHT(A1,12)
Copy the formulas down to the last row of data
Select columns B:C, and choose Edit>Copy
With the columns still selected, choosed Edit>Paste Special, Values
Delete column A.





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[Peter Atherton]

Hi

Select the addresses and run this code

Sub SplitTelenos()
Dim c
Application.ScreenUpdating = False
For Each c In Selection
c.Select
ActiveCell.Offset(0, 1) = Right(c, 12)
c.Value = Left(c, Len(c) - 13)
Next c
Application.ScreenUpdating = True
End Sub

Regards
Peter