I have on row one cell A1 = 3, E1=9, I1=4.
If in cell K1 I write: =CONCATENATE(A1:I1) I get 394
But if I try to sort the cells before concatenating them with =CONCATENATE(SORT(A1:I1)) I still get the same result. Why?
Add a comment
|
1 Answer
This is due to the principle of the SORT function, which sorts rows according to data in columns. It does not sort individual cells. The example in the link explains more.
If you want to get the result that you anticipate, transpose your data and place them: A1=3, A5=9, A9=4
and perform: =CONCATENATE(SORT(A1:A9, 1, TRUE)), which gives you 349.
Or if you need to stick with row, transpose the cells in between. Use your original cell configuration and perform: =CONCATENATE(SORT(TRANSPOSE(A1:I1), 1, TRUE))
Note: Those additional parameters are required according to the SORT function help, however it seems that 1 and TRUE are used by default if they are missing.
4 Comments
Marios
ethcz great answer!
ethcz
Thank you! However I would be glad, if the original questioner would either accept the answer, or comment, if it does not meet his expectations. It has become a bad habit recently, to ignore someones answer in many cases, usually by new users (not this case). Not good for the community, IMHO :(
Marios
I am sure he will come back and check your answer. He is an experienced user. Keep in mind you don't do it only for him but for future readers. The community will support you anyway with vote ups or other ways. Don't be discouraged by that :).
ethcz
I did not want to blame, if it looks like that, my apology then... For the past several month, I just wanted to gain at least 50 reputation to be able adding comments, and although I answered several question, no single reaction. As I now have it (possibly thanks to your vote up), I am satisfied. Thank you!