split names into separate columns

[jazzdixon]

How do I split names into two separate firstname/surname columns in LO Calc?

[JohnSUN]

Have you tried Data - Text to Columns - Separated by - Space yet?

[jazzdixon]

Thanks for that. Works fine. cheers. M

[robleyd]

What about names with multiple spaces? E.g. Vincent van Gogh or Manfred von Richthofen.

[JohnSUN]

Good example. I’ll add Jerome Klapka Jerome on my own. Which word is the first name and which is the surname?

[robleyd]

Or for a well known? name, John Paul Jones (Musician). Aside -today I learned he was born four days before me!

For a little light reading, see Falsehoods Programmers Believe About Names – With Examples

If you want a reminder of how much you (think you) know about some aspects of programming, GitHub - kdeldycke/awesome-falsehood: 😱 Falsehoods Programmers Believe in will entertain you

[JohnSUN]

@robleyd It’s cruel! I tried very hard to stay serious but after point 23, I fell off my chair and read the rest of the text lying on the floor. Thanks!

[robleyd]

OK, just to completely ruin your day, let’s imagine someone named John Null - and yes, that is a real name. I won’t mention little Bobby Tables.

[Lupp]

1. Compounds are bad data.
2. Compounds are very bad as data.
3. If you want to use a compound, you need to define a syntax telling where to split.
4. You also need to define the order of the parts depending on … (nobody knows)
5. You will always forget relevant exceptions.
6. Telling the compound was a name isn’t useful in this context.
7. If you believe in names, join the numerologists.
8. A “Western” compound name should always start with the surname being the MSP.
9. Thinking of three helping sites on AOO / LibO I contributed to, the ordinal of this question among those on the same topic surely is >300. There never was a satisfying answer.
10. Calc wisely(?) doesn’t offer a SPLIT() function. (LibO Basic does.)

[eknepfler]

I’ve dealt with this problem for many years and have come to realize that the “things programmers believe about names” warning is a serious one and this is not easily solved. I recently built a new PC and vowed to only use Libre and not MS, but I keep my old PC around to split names because it uses Ablebits solution in Excel, the only good solution I’ve ever found.

Writing formulas to do this in Libre will ultimately drive you mad if you process thousands of names per week as I do - there’s just so many weird variations you need something really smart to handle them all reliably - and if you have access to Excel, the AbleBits solution is very good: How to separate first and last name in Excel

I’m sure a similar complex solution has been coded somewhere in PHP or something and the relevant columns could be exported to CSV and processed without Excel/AbleBits, and one day I hope to come across something like that.

[eknepfler]

oh there’s a browser plugin version too for Google Sheets but I haven’t tried it
this might solve my dependency on the Excel version in fact.

Split Names - Google Workspace Marketplace

[Lupp]

The comments above (a long one by myself) seem to prove that.

1. there is no solution.
2. something that looks like a solution cannot be generally applicable.
3. the question whether a culturally neutral solution would be possible at least to some extent has not been discussed at all.

Without actually knowing “AbleBits” I therefore dare to claim that it cannot solve the problem (as I see it) in a general way at all. On the other hand, a solution that often leads to satisfactory results, but cannot safely avoid serious errors is dangerous.

Splitting thousands of names weekly with some software makes an accompanying plausibility check impossible. It can be irresponsible.

Therefore again: A name which exists only in compound form is no name for IT at all.

[Partly translated from German with the help of www.DeepL.com/Translator (free version)]

We have lots of people in Germany coming from different cultures with completely different concepts about how a name should be formed.

My personal notions lead to the decision to always put the “surname” (meant as the most significant part for sorting!) in the first position. And since surnames can consist of more than one “word”, and may even contain dashes and the like, I append a comma to the surname as a mandatory separator if I am forced by some reason to give the name as a compound. What follows then (if anything) is supposed to be an individualization of no specific syntax. As soon as everybody in the world has accepted this, we can safely split compound names. Tell me, please. Alas! Again refering to cultural differences, I don’t expect your call tomorrow. Meanwhile I will strictly dissuade from splitting compound names by software (formulas).

[Przemo]

Solution for 2 dimensional universe where people have only 1 first name, 1 surname, max length is 100 characters and the separator is " "

A1 contains “John Smith”

first name =LEFT(A1,SEARCH(" ",A1))

surname =MID(A1,SEARCH(" ",A1)+1,100)

[robleyd]

And what about the other 87% of cases