trick to perform multiple sort fails; due to a bug?

Question

In Excel 365 you can use the SORT function. However this function can not sort in multiple columns as the option in the ribbon can do very well.

To get a solution I concatenated row-wise the relevant cells in an extra column and applied the sort-function on that column.
There I got a strange result. May be it is caused by the use of accents in the words. But still it is strange that ordinary sorting of the word parts gives the right order and a combination of those word parts with some other text does not sorting properly.

See my sheet, in red the most relevant cells with strange results.
My data consists of very many word parts belonging to different or the same record number. For each word part I want a string containing all relevant record nrs in TEXT-"000" format.

Who can solve this please?
Its a pity that I can not upload the excel sheet itself!

Answer 1

Hi @Han_calcul

If you concatenated row-wise the relevant cells, the data in an extra column with "000" format won't be recognized as numbers, then it would not be sorted from smallest to largest.

I would suggest you use Sortby function to sort the contents of a Text and Number based on the values in a corresponding range.
Then you could use If function to add "0" before the two-digit number to meet the format of "000"", and use CONCATENATE function to combine them in TEXT-"000" format.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

@Emily Hua-MSFT Thank you very much! You learned me the sortby function!
With that function there is no need for a trick concatenating columns row-wise. One can execute a multiple sort very easy with sortby.

Simply sortby(col1;-1;col2;1) worked perfect.
I didn't know of sortby, you helped me very good and fast!