Forum Discussion
Sort and filter numbers when there are letters before.
Im having an issue with sorting a list. the format of the column I want to sort is "ABC-1000" and when i got to row "ABC-10000" it inserted the 10,000 rows in between the 1010 and 1011 rows. Without having to change the format I'm using to name the rows, how can i sort and filter this properly?
2 Replies
Let's say your values are in D2 and down.
Insert two empty columns to the right, in E and F.
In E2, enter the formula =LEFT(D2,FIND("-",D2)-1)
In F2, enter the formula =--MID(D2,FIND("-",D2)+1,100)
Fill down from E2 and F2.
Sort the entire range on columns E and F instead of on D.
HansVogelaarPerfect, that worked. thank you. I actually only needed the "MID" Formula since all of the initial letters are the same but its good to know in case I have a worksheet later on where the initial designator changes as well. Thanks again for your help, its greatly appreciated.
