Overcome Excel’s Sort Function Shortcoming

I have a spreadsheet that lists products with identifiers that go from C1 up to C1267. Since they are all unsorted, I tried to use Excel’s Sort function to put them in order, but then I ended up with C1, C10, C100, etc. What am I doing wrong?

What you don’t realize is that, because the first character in each cell is a letter, Excel is sorting the data as text. As a result, it first sorted all the cells starting in C1. It wasn’t until it got to the 62nd entry that it got to C2.

The solution is to use the LEN, RIGHT and LEFT functions to convert the ID format: adding zeros before the numbers, so C1 becomes C001, C2 becomes C002, etc. The conversion formula looks like this:

=LEFT(C1,1) & RIGHT(“000” & RIGHT(C1,LEN(C1)-1),3)

Now, when you sort by the newly formatted entries, you get the desired results.