Sort Left to Right based on a Cell Value

[srizki]

Hi all,
I have data that I sort every month. I want to sort the data based on the column values as follows.
[TABLE="width: 125"]
<tbody>[TR]
[TD]0517HZJ036241005
[/TD]
[/TR]
[TR]
[TD]0517HZJ307583003
[/TD]
[/TR]
[TR]
[TD]0717HZJ036241005
[/TD]
[/TR]
[TR]
[TD]0417HZJ036241005
[/TD]
[/TR]
[TR]
[TD]0417HZJ307583003
[/TD]
[/TR]
[TR]
[TD]0417NNJ036241005
[/TD]
[/TR]
[TR]
[TD]0417NNJ279495002
[/TD]
[/TR]
[TR]
[TD]0417NNJ354507001
[/TD]
[/TR]
[TR]
[TD]0517NNJ036241005
[/TD]
[/TR]
[TR]
[TD]0517NNJ279495002
[/TD]
[/TR]
[TR]
[TD]0517NNJ354507001
[/TD]
[/TR]
</tbody>[/TABLE]

So, this is what I want, I want to sort by the last digits of invoice numbers, such as, 7001, 95002, 83003 etc. so all invoices will be together and will be sorted by month, So it will be easy to find what month(s) is/are missing.

Please advise.

Regards,

 

[Joe4]

Use LEFT, RIGHT, and MID function to extract the portion that you want to sort by, and sort on those calculated values.

 

[Sandie1712]

Hi Sohail

i can think of two ways of cracking this concern

1) simply apply filters, >> Text Filters >> Ends with

2) Add a helper column and apply =RIGHT(B2,5) and thereafter you can filter of required invoice number.

Hope it'll help

 

[srizki]

Thank you Sandie,

I used text to columns approach, I separated the way I wanted and sorted it. But it would have been nice to sort without going through an extra step.

Thanks

 

[srizki]

Joe,

Thanks for your always help.
I used text to columns, that worked out well, but had to do more work, that is not always possible. I wish Microsoft add this feature.

Best regards,

 

[Joe4]

I wish Microsoft add this feature.

Add which feature? It already has sorting capabilities.
You can do what you want without Text to Columns by using formulas to extract the values that you want to sort by, like Sandie and I mentioned.

 

[srizki]

Joe4,
Sorry for the late reply.
Actually I have the following on column A,

\\enterwest.net\shared\Allshare\Houston JIB Accounting\Outside Operated JIB's\sohail's Group JIBs\SOHAIL RIZKI UPLOADS\GD America Production Co\07-2017\0717-0070025032.xlsm

This is where I wanted to sort the column by the filename.

Best regards,

 

[Joe4]

Say your entry is in cell A10. Use this formula to extra just the file name:

=TRIM(RIGHT(SUBSTITUTE(A10,"\",REPT(" ",99)),99))

(as long as your file name is less than 100 characters long, it should work)

Then you can sort by this column.

 

[srizki]

Thank you very much Joe.

 

[Joe4]

You are welcome.