CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Diganta Bharali
Diganta Bharali

Reputation: 243

How to custom sort in Excel

I have a set of values in Excel.

actionDetails_1_url
actionDetails_3_generationTime
actionDetails_2_url
actionDetails_10_url

When i sort them I get

actionDetails_1_url
actionDetails_10_url
actionDetails_2_url
actionDetails_3_generationTime

But i want it like this

actionDetails_1_url
actionDetails_2_url
actionDetails_3_generationTime
actionDetails_10_url

How to achieve that

Upvotes: 0

Views: 77

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521008

One option would be to create a new column containing the number which occurs in between the underscores, and then to sort your data on that computed column. Assuming the value actionDetails_1_url were in cell A1, here is a formula to extract the number:

= MID(A1,
      FIND("_", A1, FIND("_", A1) + 0) + 1,
      FIND("_", A1, FIND("_", A1) + 1) - FIND("_", A1, FIND("_", A1) + 0) - 1)

Additional step:

Copy this computed column into a third column, cast it to numeric, and then perform the sort. If you just sort on the column coming from the formula above, then you still get the same behavior, because it will be a lexigraphic (string) sort.

A better design option going forward might be to maintain the state you need for sorting.

Upvotes: 1

Related Questions