CODEWITHSUNDEEP
google-sheets
Max
Max

Reputation: 940

Sorting letters in google spreadsheets

I need a formula to sort letters in google spreadsheets. For example, if I have A1:E1 (b,b,c,a,d), I would like them to be sorted in F1:J1 (a,b,b,c,d).

I use this array formula {=CHAR(SMALL(CODE($R5:$V5),COLUMNS($Z:Z)))} to do this task in excel, I tried it in spreadsheets but it doesn't work. Any ideas, please? Thanks.

Upvotes: 1

Views: 230

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

@Max's answer is the best one because it expands the array, but just for the record this does work in Google Sheets if you pull it across:

=ArrayFormula(Char(Small(Code($R5:$V5),Columns($A:A))))

It may be that the original formula didn't work because the default in Google Sheets is only to go up to column Z and you would need 4 columns to the right of that.

This would expand

=ArrayFormula(Char(Small(code($R5:$V5),COLUMN(R:V)-COLUMN(R:R)+1)))

Upvotes: 2

Max Makhrov
Max Makhrov

Reputation: 18707

=TRANSPOSE(SORT(TRANSPOSE(A1:E1)))

Note:

  • double transpose is used because sort function works for columns only.

Upvotes: 2

Related Questions