Splitting columns in Calc

classic Classic list List threaded Threaded
5 messages Options
Pat Brown Pat Brown
Reply | Threaded
Open this post in threaded view
|

Splitting columns in Calc

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

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: Splitting columns in Calc

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

On 21/04/2020 12:37, Pat Brown wrote:

> 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
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Splitting columns in Calc

In reply to this post by Pat Brown
At 02:37 21/04/2020 +0200, Pat Brown wrote:
>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  


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Splitting columns in Calc

In reply to this post by Pat Brown
On Tue, 21 Apr 2020 02:37:33 +0200
Pat Brown <[hidden email]> wrote:

> 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.

> Thanks,
>
> Paddy


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

mikey mikey
Reply | Threaded
Open this post in threaded view
|

Re: Splitting columns in Calc

In reply to this post by Pat Brown
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.
______

First name for cell A1
=REGEX(A1,"(.+) ([^ ]+$)","$1")
(returns all but the last word, unless you have trailing spaces.)
Last name for cell A1
==REGEX(A1,"(.+) ([^ ]+$)","$2")
(returns the last word, unless you have trailing spaces.)

On Mon, Apr 20, 2020 at 7:39 PM Pat Brown <[hidden email]> wrote:

> 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
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy