This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Latest Stories

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.

There are over 30 million small businesses in the U.S., and many of them are optimistic in their outlook. Are you familiar with the obstacles and opportunities they are facing? Test your small business acumen with this quiz sponsored by Chase Ink®.