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

Test yourself on these often confused words

The spelling checker on your word processing program can do only so much to flag problems. Your best insurance is to learn the troublesome words that trip up writers and use them correctly by the standards of formal, written English.