How to Fix Excel Not Sorting Numbers Correctly

Managing data in Excel is a common task, but sometimes, issues arise when sorting numbers. This problem often occurs when additional non-printable characters, usually hidden from view, are included with your data. These characters can disrupt sorting, leading to unexpected results. In this comprehensive guide, you will learn how to tackle Excel not sorting numbers correctly by addressing formatting and hidden character issues.

Ways to Fix Excel Not Sorting Numbers Correctly

Identifying Hidden Characters

When your data includes hidden characters, Excel may struggle to sort it correctly. When copying data from external sources, such as the internet, users often bring in these characters. To check for these hidden characters, you can utilize the LEN function:

1. Click on an empty cell in the same row as the first cell with data that you want to check.
2. Type =LEN( in the cell.
3. Select the first cell in your data.
4. Type ) and press Enter. The formula calculates the number of characters in your cell including the spaces in between.

5. To apply the formula to all your data, click and drag the fill handle in the bottom right-hand corner of the cell to cover your data range.

6. Compare each value to the number of visible digits in the corresponding cell.

If the result of your formula is higher than the number of visible digits in your cell, this indicates the presence of additional, non-visible characters. For example, if the cell displays a single digit but the LEN function returns a value of two, hidden characters are present.

READ ALSO: How to Fix Excel Not Auto Calculating

Removing Non-Printable Characters

To resolve the sorting issues caused by non-printable characters, you need to remove them. Excel provides the CLEAN function for this purpose:

1. Find an empty cell in the same row as the first entry of your data.
2. Type =CLEAN( in the cell.
3. Select the top cell of your data.
4. Type ) and press Enter. The new cell should now contain only the number, with non-printable characters stripped away.

5. To apply the formula to all your cells, click and hold the fill handle in the bottom-right corner of the cell, and drag downward to cover all cells where you want the formula to apply.

6. These cells should now be devoid of non-printable characters.

6. You can verify this by using the LEN function as described earlier.
7. Copy your newly cleaned column.
8. Right-click in the first cell of the original data and select Paste Special > Paste Values. This pastes the cleaned values.

10. You can now delete the column you created to clean your cells. Sort your data again to check if this resolves the issue.

Removing Spaces

While the CLEAN function is effective at removing many non-printable characters, it doesn’t address all of them, particularly spaces. The CLEAN function does not remove spaces because Excel utilizes ASCII code 32 for them. To remove spaces from your numbers, you can employ the TRIM function:

1. Select an empty cell in the same row as the first number in the list you’re trying to sort.
2. Type =TRIM( in the cell.
3. Select the top cell of your data.
4. Type ) and press Enter. This removes any extra spaces from the cell.

5. Apply the formula to the remaining cells by clicking and dragging the fill handle in the bottom-right corner of the cell to cover all the cells where you want the formula to apply.

6. All your cells should now be free of extra spaces. Copy these new cells.

7. Right-click in the top cell of your original data and select Paste Special > Paste Values. This pastes the cleaned values without the formulas.
8. You can delete your new column as it is no longer needed.
9. Try sorting your data again. If it still doesn’t work, some cells may still be formatted as text.

Ensure Cells Are Not Formatted as Text

Even after removing non-printable characters and spaces, your data may not sort correctly because some cells formatted as text can affect the sorting process. To fix this issue, you need to convert them to numbers:

1. If you notice small green triangles in the corner of some cells, indicating an Excel error, it means those cells are formatted as text. You should see a warning symbol when you select these cells.
2. Click the warning symbol and select Convert to Number.
3. If you don’t see any green triangles in your cells, type ‘1’ in an empty cell and then copy that cell.
4. Highlight all your data, right-click, and select Paste Special.
5. Choose Multiply and click OK.
6. Your cells should now be recognized as numbers.

Using a Combined Formula for Complex Cases

If you’re dealing with complex data and are unsure of the specific issue, you can combine the previous methods into one formula. This comprehensive approach can help identify and address various issues within your numbers, allowing for proper sorting:

1. Click in an empty cell in the same row as your top value.
2. Type =VALUE(TRIM(CLEAN(.
3. Select the top cell of your data.
4. Type ))) and press Enter.

5. Click and drag the fill handle of your cell to apply the formula to all cells.

6. Copy the cells, then right-click the first cell of your original data.

7. Select Paste Special > Paste Values.
8. Try sorting your new cells; they should now sort correctly.

Conclusion

These methods should help you resolve most sorting problems related to non-printable characters, spaces, and text formatting in Excel. However, in some exceptional cases, manual correction may be required if a hidden character is not addressed by these functions. Sorting problems are just one of many issues you might encounter in Excel, but by mastering these solutions, you can save time and frustration when working with your data.