Forename and Surname in one column. Extract Surname only
How are Forename and Surname separated (space, comma, anything else). Is there something like: Forename, Jr. Surname or Forename S. Surname?
The format is Forename space Surname. No initials.
See also: Split Function in LIbreOffice.
Similar questions -in specific concerning compound names- were asked more than once. I always wonderd how the qustioners felt safe with an assurance concerning the syntax to be used when splitting. Surnames cannot be assured to be a single word, even if we accept the term “word” to mean “not containing a space”. After all nowadays nearly everywhere live people coming from elsewhere …
Names and their syntax depend heavily on the cultural background. This may also apply if a latin transcription was used to make the names readable for Western eyes.
Since every data provider should know this, I wonder again, for what reason so many people need to work with data containing names only as compounds.
Hello,
try:
=REGEX(A1;"\b[:alpha:]+\b$") or (simpler) =REGEX(A1;"[:alpha:]+$")
(assuming name is in cell A1)
or, if you can’t assure that there are no superfluous space characters in your names, use:
=REGEX(TRIM(A1);"\b[:alpha:]+\b$") or (simpler) =REGEX(TRIM(A1);"[:alpha:]+$")
Hope that helps.
Thanks for that reply. The first simple expression works well in LibreOffice. I would not have been able to come up with that in a month of Sundays as not familiar with ICU expressions.
Would the same syntax work in MS Excel do you think?
Have tried in Apple’s Numbers which has REGEX but also REGEX.EXTRACT and the arguments seem slightly different from calc.
Would the same syntax work in MS Excel do you think?
I’m not aware that Excel has a function called REGEX and if, don’t know which regular expression library Microsoft might use. Sorry … but my latest Excel version is from 2003 and even this is not used regularly.
With “normal” Calc functions:
I do not know if these functions ( LEFT()/RIGHT()/LEN()/SEARCH() ) work in the latest $xcel, because I have not M$ $xcel since 10+ years. But I suppose these are existing and compatible functions in the $xcel too.