Splitting columns in Calc

users
#1

I have a spreadsheet that includes a column consisting if people's names. I
want to split this into two columns. I want one column with the given names
and the other with their family names. This requires taking the last word
in the column and creating a new column with this word. Is there a way to
do this in Calc?

Thanks,

Paddy

#2

Hi Pat.
The way I do this is to insert 2 new columns, use a formula to put given
and family names in the 2 new columns.
When finished, copy the 2 new columns and Paste Special, text only, back
over the 2 columns. This overwrites the formulae with the names as text.
Delete the original column of given+family names.
Steve

#3

I have a spreadsheet that includes a column consisting if people's names. I want to split this into two columns. I want one column with the given names and the other with their family names. This requires taking the last word in the column and creating a new column with this word. Is there a way to do this in Calc?

Here's one way. Suppose the existing names are in column A, starting in A1.
o Go to Tools | Options... | LibreOffice Calc | Calculate | General Calculations, and ensure "Enable regular expressions in formulas" is ticked.
o If you have anything in columns B and C, insert two new columns, so you have columns B and C empty.
o In B1, enter =LEFT(A1;SEARCH(" [^ ]+$";A1;1)-1) and fill down the column as needed.
o In C1, enter =RIGHT(A1;LEN(A1)-SEARCH(" [^ ]+$";A1;1)) and fill down the column as needed.
o Select columns B and C.
o Copy.
o Paste (back over the same columns), but using Edit | Paste Special

Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In

the Paste Special dialogue, ensure that Text is ticked but Formulas is *not* ticked.
o If desired, delete your original column A (so that the new columns now become columns A and B).

I trust this helps.

Brian Barker

#4

I have a spreadsheet that includes a column consisting if people's
names. I want to split this into two columns. I want one column with
the given names and the other with their family names. This requires
taking the last word in the column and creating a new column with
this word. Is there a way to do this in Calc?

Note that the family name may not always be the last word. There are
various cultures where the family name comes at a different position in
the order, and even in English culture the family name may consist of
more than one word.

#5

A solution using the REGEX function can be viewed here:

(Flat OpenDocument Spreadsheet format:)
https://drive.google.com/open?id=13NsidjowVUqKWaUgsU-qL4gFQaP1BENg

On my computer, Google Chrome shows you the source code for the file.. you
need to save that link as an ".fods" file, then it will open in Calc.

(LibreOffice Calc Format:)
https://drive.google.com/open?id=1kAwo38wZzWXd11W8biVV13kSY8sCnNVX

On my computer, Google Chrome wants to open this file with Google Sheets.
But Sheets doesn't understand REGEX. Download it as an .ODS and it should
open.