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

[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