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 REPORT

Cybersecurity threats proliferating for midsize and smaller businesses

This report details how SMBs can properly protect private information from breaches, design and implement a cybersecurity policy, and create safeguards for training and education.

QUIZ

News quiz: Senate health care bill in the spotlight

Reports related to the Republican bill to repeal many provisions of the PPACA, other tax issues, and the giant AICPA ENGAGE Conference offered a diverse reading list for June. See how much you know about recent news with this short quiz.