Apr 18 2021 12:01 AM
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!
Apr 18 2021 01:36 AM
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.
Apr 18 2021 01:36 AM - edited Apr 18 2021 02:03 AM
To achieve what you are trying, you need a Helper Column.
=MID(X20,FIND("#",SUBSTITUTE(X20,"_","#",2))+1,255)
For neatness later on you may hide Helper Data & you find this.
Apr 18 2021 09:47 PM