Splitting Strings in Spreadsheets?

May 30, 2008 6:49 PM

Using OpenOffice Calc (Spreadsheets) - Is there any way to divide the strings in one column into two by the space in each cell?

I've got a contact list formatted in CSV - only problem is that the whole name for each contact is connected in the one column and I want to. Is there any way to automate this?

posted by l33tpolicywonk to Computers & Internet (5 answers total)

only problem is that the whole name for each contact is connected in the one column and I want to

This is unclear. You want to what?
posted by beagle at 7:11 PM on May 30, 2008

Response by poster: So I have a column in this spreadsheet labeled "Name". It has the names in the format FIRST (Space) LAST. Can I easily split FIRST and LAST into two columns?
posted by l33tpolicywonk at 7:38 PM on May 30, 2008

Best answer: this search in google came up with some stuff:

openoffice text to column
posted by ouchitburns at 7:49 PM on May 30, 2008

Best answer: The macro Text to Columns wins. Thanks ouchitburns!
posted by l33tpolicywonk at 7:54 PM on May 30, 2008

You already have a best answer, but here's another thought:

If you don't want to go the macro route, you can

* select the column and copy it
* paste it into a text editor
* search and replace in the text editor - search for " " (space) and replace with tab (usually entered as "\t", but it depends on the text editor)
* copy the whole thing and paste back into your spreadsheet

Note that you may need to insert a column next to the Name column before pasting; otherwise you'll wipe out the data in the next column.

I often find this approach faster and easier than writing a formula or using a macro.
posted by kristi at 12:18 PM on May 31, 2008