Sort and filter numbers when there are letters before.

Copper Contributor

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.

@Hans VogelaarPerfect, 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.