Apache OpenOffice Community Forum

I have a column of data like is seen below.

Joe Blow, Los Angeles, CA
Jane Doe, The Netherlands
John Jeff Spaniard, Madrid, Spain
Joe Downunder, Perth, W. Australia
Asian Guy, Jakarta, Indonesia

What I need to do is split the column into two columns, after the first comma (and ignore the second comma). See below (I'm using the | to designate a column) so that the name of the person is in one column and their location is in another column. How to accomplish this in Calc?

Joe Blow | Los Angeles, CA
Jane Doe | The Netherlands
John Jeff Spaniard | Madrid, Spain
Joe Downunder | Perth, W. Australia
Asian Guy | Jakarta, Indonesia

Thanks in advance!

If Joe Blow, Los Angeles, CA is in A2 then...
=LEFT(A2;FIND(",";A2)-1) returns Joe Blow
=MID(A2;FIND(",";A2)+2;255) returns Los Angeles, CA

Ken Johnson

KJ's formulas should work fine; here's another approach...

You can't use Calc's Data > Text to Columns feature because it will divide the data at every comma. But, you can change the first comma to some other character, then split on that.

Edit > Find & Replace
Search for: ^([^,]+), *(.*)
Replace with: $1!$2
Options: Regular expressions: YES
Click "Replace All"

Then, use Data > Text to Columns; Separated by > Other: !

Thank you so much, works perfectly!!

hello friends...
I have a file of 300 pages written by givng spaces to look like 2 columns. now i need to select the contents of a single column for all the pages at a time...How is this possible....

waiting for the reply.....
Thank you....

For each of those columns you will first need to insert a column to its right side then use Data|Text to Columns with Separator options being Separated by Space and Merge delimiters.

Ken Johnson

Hold on, are we talking about a spreadsheet document or a text document?

Yes, but someone has inserted a new question: http://user.services.openoffice.org/en/ ... 01#p145401 and I can't tell if it's really a continuation of the Calc topic or a new topic that should be split off.