Ask Your Question

Calc: How to convert a comma separated row to a column?

asked 2018-07-01 22:38:56 +0200

Calvin87 gravatar image

I'm composing an email to many recipients. Copy/pasting from gmail's To: field to Calc gives me one long row of email addresses, separated by a comma.

I need to convert from this single row to a single column, so I can sort the column alphabetically and remove duplicate email addresses. How can I do this?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-07-02 04:09:55 +0200

updated 2018-07-02 04:13:34 +0200

To exclude the duplicates from a column, DataMore filtersStandard Filter may be used.

  1. Select the column;
  2. DataMore filtersStandard Filter;
  3. Change the firts Field name to -none- to not filter by values;
  4. Expand Options if not yet expanded (using + on the left of the Options heading below the criteria);
  5. Select No duplications;
  6. You may select Copy results to: and define a range (e.g., a single cell in another column, starting with which the output will be done);
  7. OK.

If [6] is selected, the cells in the output range will contain the filtered-by-duplicates cells. If [6] is not selected, then the filter will just hide the rows containing duplicates; selecting the column and copy-pasting to a text will not contain the hidden cells.

edit flag offensive delete link more


Perfect! Cheers :)

Calvin87 gravatar imageCalvin87 ( 2018-07-02 05:20:31 +0200 )edit

answered 2018-07-01 23:04:48 +0200

David gravatar image

updated 2018-07-02 03:06:41 +0200

Since you have the comma-separated row, it must be a single cell with text with separators. First, break the cell to individual cells by the separator; do that using the DataText to Columns command, and select the proper separator. Then you will have the actual row with cells holding the individual addresses.

To turn the row into a column (or "transpose", which is the usual technical term), follow the instructions in this previous Q&A.

Once that is done, Select the column you wish to sort (e.g., by clicking the Column heading, A, or B, or whatever), then from the menu bar, Data > Sort... and set your choices (e.g., "Column A" with the Ascending radio button clicked).

There is then the matter of removing duplicates.... You can do that manually (of course) but if you have a lot of data, that can be automated. I'll either pick that up later, or someone else my add that in to another answer.

edit flag offensive delete link more


(oops, added my reply in the wrong spot)

Thank you, but unfortunately that didn't work for me. Trying to transpose the whole row just resulted in another row, not a column. Same whether I selected a single cell to paste to, or highlighted a blank column.

Oddly, it seems the comma is not being recognized as a separator. The entire list of 300+ email addresses appears as one long string in one row. Same behaviour when I copy from notepad instead of from the To: field directly.

Calvin87 gravatar imageCalvin87 ( 2018-07-02 02:55:29 +0200 )edit

What you need first is to break the single cell to individual cells, which then may be operated as @David suggested. First, select the cell, and use Data->Text to columns command, and select the proper separator. Then, you will have the actual row of individual addresses, which may be transposed.

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-02 03:00:49 +0200 )edit

I finally got this to work, by setting the delimiter to a single quote ' instead of the default double quote ". Thank s all for your help!

Calvin87 gravatar imageCalvin87 ( 2018-07-02 05:19:44 +0200 )edit

@Mike Kaganski - Thanks for the tweak (and additional info in new answer). Turns out it was vital in this case! I (wrongly) assumed the CSV had already been successfully imported....

David gravatar imageDavid ( 2018-07-02 13:03:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-07-01 22:38:56 +0200

Seen: 685 times

Last updated: Jul 02 '18