Overcome Excel's Sort Function Shortcoming

BY STANLEY ZAROWIN

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.

SPONSORED VIDEO

How KPMG is innovating the audit

KPMG's global audit team is using cognitive technology and alliances with tech and university partners to drive audit innovation. See how.

SPONSORED REPORT

States look to unclaimed property for revenue

This free report outlines the escheat process, common types of AUP, how different states are handling it and how companies can plan for potential audits and liabilities.