I have several hundred rows of data where I need to find the last word in a String and paste it into the adjacent column then delete the last word from the original string. (Splitting the last word)
The range will be based on the user selection. The code will then loop through all the rows splitting the last word from the string.
EXAMPLE
The Park Gove Dublin
This would become
The Park Grove -Next Cell- DUBLIN
Appreciate any help. Thank you
Assuming your data is in column A starting in row 2, try:
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
If you want it to be without VBA then the following will do what you want but (I think this is different to your requirements) place the two split strings into 2 new cells
ie if your String is in A1
then in B1
and in C1Please Login or Register to view this content.
Please Login or Register to view this content.
If someone has helped you then please add to their Reputation
I am trying to solve this problem but found Mumps1 answered really nice.
1. Thank those who have helped you by clicking the Star * below the post.
2. Please mark your post [SOLVED] if it has been answered satisfactorily.
Sincerely,
Farid
This is very similar to Mumps' solution but uses the ActiveCell to determine what to update. Anything below the ActiveCell in the same column as the ActiveCell gets updated so giving the user the choice of what data to update.
Please Login or Register to view this content.
Please click *Add Reputation if I've helped
To do it exactly as youve requested though, you'll need to add a button with the following code:
The highlight the cells you want to run it on and press the button.Please Login or Register to view this content.
pjwhitfield - This code is very handy. However if I click the button twice it clears the new cell with the last word. Whereas the code above keeps stripping out the last word from the string.
kadeo this works perfect. Are you able to tweak it so it only looks at a selected area instead of the whole column?
This should do that. As long as you only select data in one column at a time it should work fine.Originally Posted by mtilbury
Please Login or Register to view this content.
Ahhh the inclusion of TRIM should resolve that, what was happening is the space before the last word remained on the end of the string so when you run it again it splits on that lonely space and returns nothing, amend
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Works great. Thank you
pjwhitfield - Thank you this works as well. Thank you. Makes sense about the 'space' being picked up. Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks