rearrange first last and middle name in a cell

Hey all,

Basically I have one cell that can look like this:

IN RIGHT OF AKA PAUL A HERMAN,

And I want to have it look like this:

HERMAN PAUL A

So:

1) extract content to right of AKA
2) If comma exists at end, remove it
3) Rearrange the name so it's last, first, middle rather than first, middle, last

I have been working on this and this is what I have:

Please Login or Register  to view this content.

I get compiler error and it highlights the FIND function. It appears I cannot use excel formulas in a VBA script. Anyone have any solution how I can extract the first, last, and middle so I can rearrange them in my VBA function?

Thanks for response

johnmerlino,

Find is a worksheet function, so to use it in VB, it has to be prefaced with WorksheetFunction. like so:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Thanks for response. I did as you suggested and I now get the error "Unable to get the find property of the worksheetfunction class":

Please Login or Register  to view this content.

The change to instr now gives me a "type mismatch" error highlighting this line:

Please Login or Register  to view this content.

with this code:

Please Login or Register  to view this content.

Note that now I am just targeting A1, for example, but obviously I will need to change that to A1 & i because I would be iterating the length of the column. I just do this now so it makes it easier to troubleshoot.

Basically, in cell A1 I have something like this:

Please Login or Register  to view this content.

In cell B1, I have something like this:

Please Login or Register  to view this content.

So my VBA above intends to change A1 to this: Stephen Jen S and then which ever is greater length (e.g. Stephen Jen S or Stephen Jen) output it to cell C1.

Thanks for response.

Thanks a lot for all the responses

@snb, I tried your technique but it threw an error "Argument Not Optional" highlighting "Space".

@Marcol, Then I tried your technique and it worked for one cell. So I modified it a little so it loops through length of column A:

Please Login or Register  to view this content.

This above returns something like this:

Please Login or Register  to view this content.

But rather than this:

Please Login or Register  to view this content.

My intention was to pull the name with the longer length when comparing all of Column A and B (so it wouldn't just be comparing B1 and A1 but perhaps B3 and A2) like this:

Please Login or Register  to view this content.

It may be a minor change that fixes this, but I don't fully understand your code.

Thanks for response.

Here's actually a visual example of what I was looking for:

Please Login or Register  to view this content.

Thanks for response.

How about posting a workbook with some sample data?

Up 'til now you have been given 5 or 6 potential solutions, based on 1 line copied from the forum, that would appear to be insufficient.
What is your last post meant to represent? Two columns of data? Remove duplicates?.......

A bit more input from you would not go amiss.

Thanks for response. I don't have worksheet right now, but I will try to put fake data. I been trying to do it on my own.