Issue with primary column not sorting properly

mcaulism

Good morning community,

I need help with one of my sheets not sorting the primary column properly. This sheet has been active for a few years now and no issues. The primary column "asset tag #" is a 4-digit number starting with 0001. When I sort with or without a filter, the numbers are not in sequence. About 1/3 of them move to the top while the rest of them have a leading ' .

Pauline J

Hello @mcaulism

I have had this issue sometimes as well. I have found that sometimes numbers are entered as text, even though they look like numbers. I recommend selecting your column and formatting all of the entries as numbers — and see if that helps, if you haven't already tried it.

Pauline

mcaulism

@Pauline J It is a primary column so it should be text/number.

Am i missing something?

Pauline J

@mcaulism That's correct, however, notice that there is an ' before the number 0001? That means the cell is treating the "number" as a text string — so it will not sort properly. The apostrophe needs to be removed, so that it is truly a number. A way to do this is to sort by your premary — the text cells should group together. Then you can remove the apostrophe. If you have too many to do by hand, I recommend creating a "helper" column. Copy the text cells and paste them into Excel, format as a number, and then copy/paste into the new column. Do a check to make sure that the numbers are correct, matching the number in the Primary. If so, you should be able to copy those new numbers from the helper cells into your Primary column — and now you'll have all numbers, no apostrophes.

If there is a better way to do this conversion, I'd love to know — but once it's cleaned up, you shouldn't have an issue.