3-level sorting including sorting a column by the number of percent sign in each cell

Hi All,

I have one worksheet that contains over 80,000 rows of data distributed across 4 columns A-D.

As per attached example:

Sheet1 has a sample unsorted data, where ColD has the core data for this question.

ColD has names of subjects preceded a "%" char. Each cell in ColD has a variable number of names of subjects. ColD is needed to be sorted ascending according to the number of names of subjects per each cell.

Because the name of each subject is consisted of several words, a "%" sign is added before the subject's name, so that the number of percent signs in each cell is used to sort the entire column. I manually counted % signs as: 8, 22, 4, 2, 3, 7 and 15.


Sheet2 has the same sample data sorted as desired. A three-level ascending sorting is applied for ColA, ColB and ColD where the number of percent signs is used to sort ColD.

Can this process be applied on the real large data by a smart code that counts % signs and sort data accordingly?

I do appreciate any valuable assistance with this request.

Many thanks in advance.

T.

Certainly yes Bernie. This is a genius, simple and perfect solution.
Thank you very much for your invaluable input to my problem which does not exist any more after applying your formula.
Please accept my deep gratitude and thanking.

All the best.

T.