Skip to content
Menu
Ryan and Debi & Toren
  • highpoints
  • Privacy Policy
  • R
  • tech
  • Where I’ve Been
Ryan and Debi & Toren

LibreOffice Calc – splitting contents of cells into multiple columns (e.g., splitting commas)

Posted on January 8, 2018

I periodically have to take a column of text in LibreOffice calc that has names like this “Lastname, Firstname” and split them into two columns. I figure it out every time, but then I forget how I did. So, here’s a quick tutorial on how this is done.

Open your spreadsheet with the cells that need to be split, like this:

Select the column that you want to split:

Then go up to Data -> Text to Columns:

You’ll get the following window:

This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. I selected just “Tab” and “Comma” but could also select “Space” to get rid of the extra space. However, I’m going to leave the extra space and show you one more function that can be useful in more complex situations. Once you’re done, hit “OK” and you’ll see your single columns split into two:

If you want to get rid of the extra space, there is a LibreOffice Calc function for that. Click in cell C1 and then go up to Insert -> Function. You’ll see this window:

The function you want is in the Text Category (use the dropdown menu) and is called TRIM. Simple TRIM the text in B1 and it will get rid of the extra spaces:

When you’re done with your function, select “OK” and you’ve got your spaces removed. Drag that function down and it will remove all of the spaces:

You can then copy the new column without the spaces and do a “Paste Special” into the old column, overwriting the text with the spaces. Just make sure you turn off the “Formulas” when you do the “Paste Special” and you’ll get just the new text:

Delete the column with formulas and you’re good to go:

 

 72,971 total views,  3 views today

10 thoughts on “LibreOffice Calc – splitting contents of cells into multiple columns (e.g., splitting commas)”

  1. Craig says:
    April 26, 2018 at 8:58 pm

    Lifesaver. Thanks!

    Reply
  2. Mandar says:
    June 8, 2019 at 10:01 am

    Thanks!

    Reply
  3. Simon Webb says:
    September 25, 2019 at 7:40 am

    thanks that was really useful ! 🙂

    Reply
  4. Buesi says:
    February 9, 2020 at 5:19 pm

    Thanks for the tutorial, I was actually looking for the formula to use for this problem, but couldn’t find it anywhere… I figured it out myself now and wanted to share it, because it’s waaaaaaaay faster 🙂 and more convenient (I think).

    If you have something like this in A1: Peter, Müller
    and you want to have in B1: Peter and in C1 Müller, apply this formula to B1:
    =LEFT(A1,FIND(“,”, A1))
    (what this does is just take everything that’s left of the comma and print it into B1)
    and this formula to C1:
    =RIGHT(A1,LEN(A1)-FIND(” “, A1))
    (what this does is just take everything that’s right of the space and print it into C1)

    You can also apply this to other separation forms…

    Reply
  5. JohnnyQ says:
    January 24, 2021 at 9:04 pm

    Gaahh! Libre Office is so often so weird and cryptic. Powerful, yes, but inscrutable. Anyway your how-to was just what I needed. It simplified a process and lowered my blood pressure all in one fell swoop. Cheers!

    Reply
  6. Guddy says:
    April 10, 2021 at 1:09 am

    What if some of the full names have a middle initial and some don’t?

    Reply
    1. rcragun says:
      April 20, 2021 at 2:08 pm

      It will split those into an additional column, which can be annoying. I typically just resolve that afterward.

      Reply
  7. a says:
    April 12, 2021 at 1:19 pm

    Use Edit > “Find and replace” to get rid of space. Much easier than what you suggested.

    Reply
  8. Mark says:
    April 23, 2021 at 6:13 pm

    Great! Thank you!

    Reply
  9. Pingback: Libreoffice STUFF – Cheat Sheets for Computational Biochemistry

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • advice
  • country highpoints
  • funny stuff
  • general news
  • hiking
  • memories
  • movie reviews
  • opinions
  • other
  • politics
  • R
  • religion
  • sociology
  • state highpoints
  • stories
  • technology
  • Toren
  • travel
  • website feedback
©2023 Ryan and Debi & Toren | WordPress Theme by Superbthemes.com