Cutting last 10 charecters of a cell and pasting to next cell Solved/Closed

Question

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 versionabcde      . 07032015      . 12   . David4.3010.201bcdef      . 06032015      . 11   . Johnathan2.1110.407


What I need ...

file name . creation date . size . author       . release versionabcde     . 07032015      . 12   . David        . 4.3010.201bcdef     . 06032015      . 11   . Johnathan    . 2.1110.407

Please dont worry about renaming the columns - that is just for illustration here and if I need it in my final version I can add it myself.

 Any help would be more than welcome.


Thanks
 
I really appreciate thank you messages as a payment for solving issues   :o)

TrowaD · Accepted Answer

Hi Brian,

Welcome back!

See if the following code gets you the desired result:
Sub RunMe()
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)
Next cell

End Sub

Best regards,
Trowa

Ps. I totally agree with your signature. A shame one has to point that out!

Cutting last 10 charecters of a cell and pasting to next cell

1 response

Similar discussions

Subscribe To Our Newsletter!