Google Sheets SORT comma separated text in cells using array formula

Question

I have a column with tags like a, b, c, etc. Tags in the cell are unique, well separated with ", " (a comma and a space), but not sorted. I want to an array formula to return a column with sorted tags in each cell. for example In A1:A5

c, a

b, a, d

a, c

c

c, b, a

I want an array formula in B1 to return results in B1:B5

a, c

a, b, d

a, c

c

a, b, c

After sorting in cells I can tell that tags "c, a" is the same as tags "a, c". My formula only return only one cell

=ARRAYFORMULA(JOIN(", ",SORT(TRANSPOSE(SPLIT(A1:A5,", ",FALSE)))))

score 3 · Accepted Answer · edited May 23 '17 at 12:31

ArrayFormula:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QUERY(SORT({TRANSPOSE(SPLIT(JOIN(", ",ArrayFormula(REGEXREPLACE(A1:A5,"\w+",TEXT(row(A1:A5),"0")))),", ")),TRANSPOSE(SPLIT(JOIN(", ",A1:A5),", "))},1,1,2,1),"select Col2",0)&", "),ROW(A1:A5)=SPLIT(JOIN(", ",ArrayFormula(REGEXREPLACE(A1:A5,"\w+",TEXT(row(A1:A5),"0")))),", "))&REPT(" "&CHAR(9),TRANSPOSE(row(OFFSET(A1,,,COUNTA(SPLIT(JOIN(", ",A1:A5),", "))))-row($A$1)+1)=COUNTA(SPLIT(JOIN(", ",A1:A5),", ")))),",  "&CHAR(9),0))))

I used this tip to resolve the task.

Update

With some edits, this formula can also handle comma separated phrases:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QUERY(SORT({TRANSPOSE(SPLIT(JOIN(",",ArrayFormula(REGEXREPLACE(A1:A5,"[^,]+",TEXT(row(A1:A5),"0")))),",",0)),TRANSPOSE(SPLIT(JOIN(", ",A1:A5),", ",0))},1,1,2,1),"select Col2",0)&", "),ROW(A1:A5)=SPLIT(JOIN(",",ArrayFormula(REGEXREPLACE(A1:A5,"[^,]+",TEXT(row(A1:A5),"0")))),",",0))&REPT(" "&CHAR(9),TRANSPOSE(row(OFFSET(A1,,,COUNTA(SPLIT(JOIN(", ",A1:A5),", ",0))))-row($A$1)+1)=COUNTA(SPLIT(JOIN(", ",A1:A5),", ",0)))),",  "&CHAR(9),0))))

file sample. The key is REGEXREPLACE part of formula: "[^,]+"

Thanks Max. It works for above simple case. But for comma separated phrases, it may not work properly. But your answer really sheds light on this. — visualPaul, Feb 29 '16 at 20:26

score 1 · Answer 2 · answered Feb 28 '16 at 18:59

Arrayformula and split will not work on more than one row. The best I can think of is to fill the formula down with script. You could do it onOpen or onEdit.

function fillformuladown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getDataRange().getNumRows();  
  var rngVal = ss.getRange("k1").getFormula()
  ss.getRange("b1:b"+lastRow).setFormula(rngVal)
}

Google Sheets SORT comma separated text in cells using array formula

2 Answers2