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: [email protected]
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
