Find Data, Copy Data, Paste Data (IF)

[SubZero]

Hi, I have scanned some details into a worksheet, but I need to rearrange the data and clean it. The data is company name, address and tel / fax and email. When imported each text/number string is in its own cell. eg format

company name limited
3 the orchard, sudbury tel
05555 555555 london, UK, email
someone@someosn.com

(This format is repeated many times, possibly data is in a different order but although row lengths may change, there is always data at the 1st cell of the row, afterwards there is blank row before the next record.)

What I want to do is get the tel number next to the company name on one row (company name is always the only data in row 1 of the record), so i need a macro to find the word "tel", then if there is anything in the next cell copy it, and paste it next to the company name, 1 cell on. If there is no data in the cell after "tel" we need to move to the next row and copy the 1st cell, then paste it above. I also need to do this for the second part of the phone number. (TEL 05555 55555 will always appear after each other but may be split over 2 rows) Once done I should have:
Company name Limited 05555 555555
The rest of the data on that record can be deleted, now proceed to the next record, do the same and loop...
I hope this is clear? and look forward to your help.

[Dexter]

Subzero,

I have worked through similar problems. Is your imported data in a predictable format. What does the data look like if there is no telephone number?

Dexter
St. Louis

[SubZero]

There is always a phone number, 2 eg's as copied from the page are:

ULMKE (BIR)
Birmingham Road, Saltisford, Warwick,
CV34 4TT, Tel 01926 496145 Fax 01926
415441

ULMKE (COV)
19 Coventry Road, Nr Ullenhall, Tel
02476 4545454 Fax 02476 87789787

As you can see the Name is always on one row on its own, the tel number is always there but can be spread on 2 rows. In excel I have made all data into a table using spaces, so that each word or series of numbers is in it's own cell, thats why tel is 1 cell, 01246 is 1 cell & 5454545 is another cell.

[Dexter]

SubZero,

Although I do not understand your situation completely, here is some code that I use to so some similar data manipulation on an imported text file. Maybe it will give you some ideas.

Dexter

Sub AddPINS()

'This routine copies the PINS number to each row of data

Sheets("648f710").Select
Range("C7").Select

For N = 1 To 300 'Upper number in loop represents number of plans in file.
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -2).Select
If ActiveCell.Value = "" Then Exit For
Selection.Copy
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(2, 2).Select

Next N
End Sub
Sub AddLineLabels()

'This routine copies the range named "transaction_labels" to each set of insurance data.
'The transaction labels are: CHG, PMT, ADJ

Range("Transaction_Labels").Copy
Sheets("648f710").Select
Range("B7").Select

For L = 1 To 600 'Upper number in loop represents number of plans in file.

If ActiveCell.Offset(0, -1) = "" Then Exit For
ActiveSheet.Paste
ActiveCell.Offset(4, 0).Select

Next L

Range("A1").Select
Application.CutCopyMode = False
End Sub