Split text string of Fname, Mname (if present) & Sname into two columns 1

[IcarusHallsorts]

I have a column of names which sometimes contain just first and surnames, and sometimes one or more middle names, each separated by a space.
I want to split this into just two columns, one containing First and Middle (if present) Names, and the other the surname.
For example

Jean Luc Picard -> Jean Luc | Picard
Fred Bloggs -> Fred | Bloggs
May Jane Claire Smith -> Mary Jane Claire | Smith
where text before the | is the first new column, and anything after is the second new one


How can I achieve this using either a formula or VBA?
Any guidance appreciated.

 

[Andrzejek]

How would you accommodate "double-barrelled names [that] are written without a hyphen" in your formula

Unless you declare a rule: no matter how many 'pieces' I have, the last 'piece' of the name is a Surname, all the rest belong to First name.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

How about
Vincent Van Gogh
Olivia de Haviland
Leonardo da Vinci
Daphne du Maurier
Verner von Braun
Martin Van Buren
Theobald Theodor Friedrich Alfred von Bethmann Hollweg
Guido van der Valk
???????????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

John Jacob Jingleheimer Schmidt

 

[SkipVought]

This kind of task is iterative, if using formulas or code, since the rules for identifying last name are variable.

Iterative and labor intensive. Trial and error, iteratively.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[IcarusHallsorts]

The "Rip van Winkel" structure of name is not something I had considered and it does raise a very interesting quandry. That said, it does not really apply with the data set I currently generate. Right now, I would settle for everything prior to the final space to be considered as Forename(s) with the remainder as Surname. If I did get an exception, a multi-name, undouble-barrelled surname I'd add a piece of code to search for and amend it.

 

[Andrzejek]

I am sure you can do that with a (complicated?) formula, but would the User Defined Function in VBA be sufficient? You can use it as any other built-in formula in Excel...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Deniall]

IcarusHallsorts.[ ] You state that you are prepared to accept a highly limited subset of possible name structures, where in effect you only want the last "word" in a string of words.[ ] This can be done using standard Excel functions, but it is a bit convoluted.[ ] I will illustrate one possible way by introducing some helper cells.

Assume the name to be processed is in cell A5.[ ] Place the following formula in cell B5.
=LEN(A5)-LEN(SUBSTITUTE(A5," ",""))
This gives you the total number of space characters in the full name.

Now place the following formula in cell C5.
=FIND(CHAR(1),SUBSTITUTE(A5," ",CHAR(1),B5))
This gives you the location of the final space character.[ ] It does this by temporarily replacing the final space character with a non-printable character [CHAR(1)], then FINDing that character instead.

Finally place the following formula in cell D5.
=RIGHT(A5,LEN(A5)-C5)
This is the "surname" you are seeking.

Once you have convinced yourself it works, you can combine these three formulae into a single incomprehensible formula, thereby eliminating the need for helper cells.

NOTE that this approach assumes the name string in cell A5 does not have any trailing space characters.[ ] You would have to make the formula even more complicated if you want to allow for these (but I think it COULD be done).

 

[Andrzejek]

We may never know the answers to all the questions presented here since it is hard to help someone who does not want to reply to his own posts


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

At any rate, in practice it's generally more efficient to use text to columns and manually tackle the outliers that spill across too many columns.