Chong,
Thanks for your code, worked fine

Paul

"Chong Moua" wrote in message
...
Hi Paul,

Try this....
--------------------------
Sub CopyFormula()

Dim i As Integer

For i = 1 To Sheets("Import").UsedRange.Rows.Count
Cells(i, "A") = Trim(Mid(Sheets("Import").Cells
(i, "A"), 28, 55)) & " " & Trim(Right(Sheets
("Import").Cells(i, "A"), 175)) & " " & (Mid(Sheets
("Import").Cells(i, "A"), 14, 12))
Next i

End Sub
---------------------------
Hope this helps...

Chong Moua

-----Original Message-----
Apologies for multiple postings, sent earlier but
couldn't see message
listed..just discovered system time was set to yesterday
@13:00.......

Hi all,

I am trying to automate a tedious process that is used to
strip data from
csv files.

I have a number of files that are sent on a weekly basis,
I have a macro
that imports the file and places each line of data in a
single cell, I then
use the following formula to extract the data:

=(TRIM(MID(Import!A2,28,55))&" ")&TRIM(RIGHT(Import!
A2,175))&"
"&(MID(Import!A2,14,12))

I have been copying the formula from 1:65536 as I never
know how large the
files will be, and yes some are 50,000 lines long, my
problem is that the
file is getting large and slow, what I would like to do
is have the macro
determine the start and finish point of the data and then
run the formula
against just those cells that are populated, hopefully
avoiding having to
place the formula in so many cells.

Any help appreciated

Paul




.