How can I put all rows in numeric order by serial number while ignoring the letters in front of the serial numbers? For instance, if I want all rows in order according to the serial number column when the numbers look like:
AA-1010, AA-1009, BC-9999, ZX-0236.
I want Excel to ignore the letters and put them in numeric order, so that the previous list would actually look like:
ZX-0236, BC-9999, AA-1009, AA-1010.
Thanks!
Use Text to Columns with hyphen/minus sign as the delimiter. Ignore the first column and output the second (the number) to a "helper" column. Sort on the helper column and, if no longer required, delete the helper column.
Done
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Add a helper column that takes the last 4 chars only and then sort by that column.
e.g formula
=RIGHT(A2,4)
copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks