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

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Jul 27, 2015 at 09:18 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 28, 2015 at 11:58 AM
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

abcde . 07032015 . 12 . David4.3010.201
bcdef . 06032015 . 11 . Johnathan2.1110.407



What I need ...

file name . creation date . size . author       . release version

abcde . 07032015 . 12 . David . 4.3010.201
bcdef . 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)

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 27, 2015 at 11:18 AM
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!
2
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Jul 27, 2015 at 02:44 PM
Wow Trowa ... That was quick!

I wont be able to test this until tomorrow. I will let you know how it goes.

Thank you so much.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Jul 28, 2015 at 11:51 AM
Absolutely Marvelous. Just what I needed.

Thanks Trowa - you're a star.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 28, 2015 at 11:58 AM
You are welcome Brian!
0