Sorting data question

[33tixe]

Hello there,

quick question: I want to sort an inventory sheet by a column that contains the item designator with characters and numbers like "MT_A_67" and "MT_A_8", etc. My problem is that during sorting "MT_A_67" lists before "MT_A_8" so it doesn't compare the number 8 and 67 correctly. What is the solution?

Thank you!

[#2]

@33tixe

Insert three empty columns to the right of the column with the item designators.

Select the item designators.

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click Next >.

Tick the check box for Other and enter an underscore _ in the box next to it.

Click Finish.

You now have three extra columns. You can sort the range on the number column, or any combination of columns that you want.

[#3]

@33tixe 

To achieve what you are trying, you need a Helper Column.

• Enter this in cell Y 20 & fill down:

=MID(X20,FIND("#",SUBSTITUTE(X20,"_","#",2))+1,255)

• Select entire data range X19:Y23.
• From Home Tab hit the SORT icon.
• Select Custom Sort.

• Now hit Ok.
• You find this dialogue.

• Select the first option & finish with Ok.
• You find data in desire order.

For neatness later on you may hide Helper Data & you find this.

[#4]

thanks so much

[#5]

thank you so much

[#6]

Glad to help you,,, since my method is working for you,, then you may accept it as best answer as well like ☺