Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

IcarusHallsorts

Technical User
Nov 8, 2019
9
GB
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.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
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.

 
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
 
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).
 
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
[thumbsdown]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top