I need to reorganize the data in an Excel spreadsheet column, which comes to me this way:
Charles Notley MI CSN
In other words: first name [space] surname [space] state (two letters) [space space] three letters.
I need to have the data arranged this way:
MI Charles Notley CSN
In other words: state (two letters) [space] first name (of variable length) [space] surname (variable length) [space space] three letters
This could be a manual operation for each row in the spreadsheet column, in which case I would have to run the macro for each row in the column.
I conceive the steps to be something like this, but I don’t know how to code it:
- Start at first letter in the text string.
- Highlight a variable length of letters, moving from left to right, until and including reach a space.
- CTL X highlighted section from step 2, including the space.
- Indent left.
- Right arrow 4 times.
- CTL V the highlighted section from step 2.
- Stop.
In other words, it would look like this, step-by-step:
Charles Notley MI CSN
Charles Notley MI CSN
MI CSN
MI Charles Notley CSN
Can this be done? How much extra trouble would it be to cause this procedure to automatically continue, working its way down a variable number of rows in a column and stop when it has completed the last row of the column which has data in it?
Thanks
Charles Notley