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

Report
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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 reply

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
248
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.
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
248
Absolutely Marvelous. Just what I needed.

Thanks Trowa - you're a star.
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
You are welcome Brian!