Re: [libreoffice-users] Splitting columns in Calc

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: users+unsubscribe@global.libreoffice.org
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