Reversing a "First Middle Last" Name to "Last, First Middle" Name Format

[Rick Rothstein]

This just came up (again, for the thousandth time maybe) and I came up with a different formula than I had in the past. This formula works correctly for the following name layouts...

First Last

First Middle Last

First M Last

First M. Last

First1 First2 Last

First1 First2 Last1 Last2

That next-to-last one being a name like "Mary Ann Jones" where "Mary Ann" is the full first name. The last one, on the other hand, covers a name like "Mary Ann Della Rossa" where "Mary Ann" is the full first name and "Della Rossa" is the full last name. I should note that the last one could be thought of as this also...

First Middle Last1 Last2

First M Last1 Last2

First M. Last1 Last2

which, of course, are also covered by the formula. Unfortunately, this formula will not correctly handle this arrangement...

First Last1 Last2

If you think about it, though, pretty much nothing ever could because there is no way to reliably distinguish between both of the following within the same data list...

First1 First2 Last

First Last1 Last2

as there would be no way to tell 100% of the time whether the middle "word" belongs to the First or the Last name. Anyway, a choice had to be made and since First1 First2 Last would be processed identically to First Middle Last, that was the one that was chosen.

Okay, enough of all that introduction stuff, here is the formula that will properly reverse (with an introduced comma separator) probably better than 99.99% of the names in any list you would ever have to process...

=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)

[jamilm]

=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)

i am curious to learn this. would it be possible Rick to breakdown this formula. what exactly this multple find and countif did?

[Spyder]

i am curious to learn this. would it be possible Rick to breakdown this formula. what exactly this multple find and countif did?

Rick's method:

The countif is counting if there are a number of spaces as far as I can tell.
Rick's method works with max 3 words - more than 3 words, it fails to reverse proper
Looks like a simpler version than mine, however, mine works with more than 3 names (or 2 spaces).

My method is:

=MID(MASTER!B5&", "&MASTER!B5,FIND("^^",SUBSTITUTE(MASTER!B5," ","^^",LEN(MASTER!B5)-LEN(SUBSTITUTE(MASTER!B5," ",""))))+1,LEN(MASTER!B5)+1)

B5 is the single sell with format of:
"First Last"
"First Middle Last"
"First Middle1 Middle2 Last"

note: format for Rick's or my versions MUST have at least one space, ie: 2 words

It may be complicated, but gets the job done, and the only way I could get it to work proper.


As for explaining code, the best way to learn is to dissect it yourself, and learn how each function works. That is mainly how I learned programming, computers, driving, drinking and you can guess the rest.

[KevCarter]

Hello,

I am enjoying Rick's code for switching first/last to last/first:


=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)

I use it often in my little database for running golf tournaments. I would like to turn this line into a function in VBA. I have been trying, and trying, but I'm pretty new to VBA, have yet to write my own function successfully, and can't get the code written properly. Any help would be greatly appreciated.

The way I would like to use the function is to place it in a blank adjacent cell, and write the value to the blank cell rather than changing the original, just as Rick's code does when placed in the spreadsheet.

Thank You in advance,
Kevin


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[snb]

I'd prefer:

=MID(TEXT(A2,"@ @"),SEARCH(" ",A2)+1,LEN(A2))

In VBA


function F_snb(c00)
F_snb=split(c00)(1) & " " & split(c00)(0)
end function

in Cell B2

=F_snb(A2)

[KevCarter]

I'd prefer:

=MID(TEXT(A2,"@ @"),SEARCH(" ",A2)+1,LEN(A2))

In VBA


function F_snb(c00)
F_snb=split(c00)(1) & " " & split(c00)(0)
end function

in Cell B2

=F_snb(A2)

WOW, I never got close to anything that looks like that. I have so much to learn. Thank you very much for taking the time in helping me!

Kevin