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?
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 Data→Text 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.
(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.
What you need first is to break the single cell to individual cells, which then may be operated as @dajare 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.
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!
@mikekaganski - 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…
To exclude the duplicates from a column, Data→More filters→Standard Filter may be used.
Data→More filters→Standard Filter;Field name to -none- to not filter by values;Options if not yet expanded (using + on the left of the Options heading below the criteria);No duplications;Copy results to: and define a range (e.g., a single cell in another column, starting with which the output will be done);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.
Perfect! Cheers 