Hi Guys - Im back again with another excel macro problem. I can find all sorts of advice when I google this, but none of it quite matches my needs so I thought Id ask you guys.
The situation is I have a text file that I need to manipulate with Excel. The file is a set of data where each row of data holds information like a file name, creation date, size of the file, author, and the release version. I have created a macro that so far successfully imports the .txt into Excel, and it divides most of the data into cells.
My problem is that the last cell currently holds the authors name and the release version in one cell. This is because whilst all the other cells have data the same width for each heading, the authors name varies in length. What I need to do is cut the last 10 characters from this column and paste it into the next column for all of the lines of data. The list is about 120 lines of data and this does vary each time the data is generated (about once a week) so I need to do this to every cell in column D.
Hopefully this diagram will help...
What I have...
file name . creation date . size . author and release version
See if the following code gets you the desired result:
Dim lRow As Integer
lRow = Range("D1").End(xlDown).Row
For Each cell In Range("D2:D" & lRow)
cell.Offset(0, 1).Value = Right(cell.Value, 10)
cell.Value = Left(cell.Value, Len(cell.Value) - 10)
Ps. I totally agree with your signature. A shame one has to point that out!