Hello,

When sorting a column of cells (with the sort order set to "A to Z") in Excel, the following values would be sorted thus:

C-121

C-122

C-123

C-124

But suppose you wanted the search function to interpret the hyphens as minus signs so that sorting them (again with the sort order set to "A to Z") would produce this:

C-124

C-123

C-122

C-121

How could that be done?

I tried setting it up in a number of different ways as shown below but nothing would sort in the order described, i.e. with the exception of sorting by column E, it always treated the minus sign as a hyphen and sorted in the order shown.

(The formulas in cells B11:E11 are shown below in blue and those in cells G8:L8 are shown above in red. Column B is only present for scrambling.)

Thanks

Sorry about that but from here I can't tell. In any case you can do what you want via VBA code.

Open in new window