sorting names are not done properly

[Marsoni]

Hi,
I've the below names in my Excel cells, when i'm sorting them they are appearing in below way.

<file name="Malaysian_Civil_Procedure_2013_Order_1.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_10.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_11.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_12.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_13.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_14.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_14A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_15.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_16.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_17.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_18.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_19.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_1A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_2.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_20.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_21.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_22.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_22A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_22B.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_23.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_24.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_25.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_26.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_27.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_28.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_29.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_3.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_30.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_31.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_32.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_33.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_34.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_35.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_35A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_36.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_37.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_38.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_39.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_4.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_40.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_40A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_41.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_42.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_43.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_44.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_45.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_46.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_47.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_48.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_49.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_5.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_50.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_51.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_51A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_52.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_53.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_54.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_55.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_55A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_56.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_57.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_58.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_59.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_6.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_60.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_61.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_62.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_63.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_63A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_64.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_65.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_66.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_67.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_68.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_69.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_7.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_70.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_71.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_72.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_73.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_74.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_75.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_76.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_77.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_78.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_79.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_8.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_80.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_81.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_82.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_83.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_84.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_85.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_85A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_86.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_86A.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_87.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_88.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_89.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_9.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_90.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_91.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_92.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_93.xml"></file>
<file name="Malaysian_Civil_Procedure_2013_Order_94.xml"></file>
but i want them in the below way.

Order1
Order_1A
order2
order3...

please let me know how i can do it.

Thanks

 

[oldchippy]

Hi Marsoni,

Welcome to the forum, if your data is in A2 put this in B2 and copy down, then filter the table by column B, the files will then be in order

=IFERROR(VALUE(MID(A2,FIND("Order_",A2)+6,FIND(".",A2)-(FIND("Order_",A2)+6))),VALUE(MID(A2,FIND("Order_",A2)+6,(FIND(".",A2)-1)-(FIND("Order_",A2)+6))))

 

[Faseeh]

Hi Marsoni,

Similar to oldchippy's formula but a bit more hard-coded.

=MID(MID(A2,50,99),1,FIND(".xml",MID(A2,50,99))-1)

 

[oldchippy]

@Faseeh,

They need to be sorted in numerical order

Order1
Order_1A
order2
order3...

 

[NARAYANK991]

Hi ,

In case you don't mind doing it the hard way , here is the technique , copied from :

http://www.excelforum.com/excel-general/332653-replace-using-wildcards.html

in which Dave Peterson has suggested this.

Select the data range , and apply AutoFilter.

Click on the Filter drop-down , and select a Filter , then select : Contains _?.xml

When the list of items is displayed , press CTRL H to open the Find and Replace dialog box , and replace Order_ by Order_0.

The odd man out is the 1A entry , which needs to be manually changed to 01A.

Now , if you sort the data , it will be sorted correctly.

Narayan