Sort a comma seperated list in indvidual cells into alphabetical order?

Here's a script solution, hard to write, but now that it is written, easy to use, just a click:

Copy from here in step 3 below:

    NumbersApp = Application('Numbers');
    document = NumbersApp.documents[0]; // front document
    sheet = document.activeSheet;
    table = sheet.tables[0]; // first table
	col = table.columns[0]; // first column => "A"

    // Loop through the rows
    for (var i = 0; i < table.rows.length; i++) {
        var cell = col.cells[i];
        if (cell.value() !== null) {
            var values = cell.value().split(', ');
            values.sort();
            cell.value = values.join(', ');
        }
    }

1. Open Script Editor (in the Applications > Utilities Folder.
2. Switch to Javascript upper left (as shown in screenshot above)
3. Copy-paste script into it.
4. Have your Numbers document open (having first made a backup!).
5. Click the triangle 'run' button in Script Editor.

The script will go through each cell in column A and alphabetize the values in it, ignoring cells with no values

columns[0] means Column A, columns[1] means Column B, etc. So you can easily modify the script to target another column if your values are not in Column A.

SG

Separating them is simple.

B2 =IFERROR(TRIM(REGEX.EXTRACT($A2,"[^,]+",B$1)),"")

fill across and down to complete the columns.

Alphabetizing them would be easy if it was only one or two place names per cell because you can do simple comparisons such as IFS(C2="",B2,B2>C2, C2&", "&B2, TRUE, B2&", "&C2). I'm not sure how to alphabetize three (or more) in a simple way. Sorting algorithms are repetitive/recursive processes. Three names won't require recursion but there are 6 different ways they could be ordered, plus perhaps having to check for null strings because TEXTJOIN does not consider a null string to be "blank" and will put commas between them.

Hi 'smile,

The bad news is that you'll likely have to do this 'by hand', so to speak.

A quick look at the Sort options offered in Numbers shows only two: You can sort the whole table, or you can sort selected rows by the values in one or more columns. Note that both sorts apply to the cells in the sorted rows, and depend on the content of cells in the 'sort column(s)'. Other than being moved with the cell, the content of each cell is not changed or rearranged by that sort.

It may be possible to use one of the text functions to separate the names and place them into a series of cells in a column of a separate small table, to then sort that column on the content (single place name) in each of the filled cells, and transfer them back, in order, to the 'after' column of your original table.

Late in the evening, so I'll leave that possibility to another of the participants in this community.

Regards,

Barry

Hi big_smile,

big_smile wrote:

Within each cell, I need to sort the list so they are in alphabetical order. The sorting needs to occur in each cell individually (i.e. so the whole column should not be in alphabetical order. The cells should remain in the same position, it is only the comma separated strings in each cell that should be adjusted).

The functions TEXTBEFORE, TEXTAFTER and possibly TEXTBETWEEN will separate items in each cell to other columns (I am thinking of ", " as the search string). But the next step is to sort those separated items horizontally, which Numbers does not do. Then concatenate the sorted items.

Hmm... Time for the thinking cap. I shall return if I think of a way.

Regards,

Ian.

Yes, I was happy to see the script posted. It would not have been pleasant attempting to do it with formulas and would not have been practical with up to seven names to sort. If Numbers had Excel's SORT function it would have been easier, though still would have required extra columns to separate each place name.

Before working on this more, will there be more than 3 names in a cell?