These non-printable characters can be used to provide information about formatting, such as when to insert a line break. The character doesn’t display in Excel but is still included in your cell. If you have numerical data that include these characters, you may that when you try to sort your data, it doesn’t work as expected. If this sounds familiar, learn how to fix Excel not sorting numbers correctly using the steps below.
How to Check the Number of Characters in Each Cell
One of the most common reasons that your numbers in Excel aren’t sorting correctly is because there are additional characters included with your numbers that aren’t showing in the cell. This can often happen when you copy and paste values from an outside source, such as data found on the internet. There is a quick and easy way to check if there are any additional characters hidden alongside your numbers by using the LEN function. To check the number of characters in each cell using the LEN function:
Remove Non-Printable Characters
If your cells contain non-printable characters, then Excel will struggle to sort them correctly. If you’ve tried the method above and the number of characters in your cell is too high, then you’ll need to remove any non-printable characters before you sort your data. Thankfully, there’s an Excel function designed to do exactly that. To remove non-printable characters in Excel:
Remove Spaces
The CLEAN function will remove many non-printable characters, but it won’t remove all of them. The function only removes the first 32 non-printable characters in ASCII code (0-31). If the non-printable character in your cell is outside this range, then CLEAN won’t remove it. Unfortunately, any empty space which is the most common non-printing character in Excel spreadsheets is ASCII code 32 and so isn’t removed by CLEAN. To get rid of extra spaces, we need to use the TRIM function. To remove spaces from numbers using TRIM:
Ensure Cells Are Not Formatted as Text
If you’ve removed any non-printing characters from your cells, and they’re still not sorting properly, the most likely cause is that some of the cells are formatted as text. When you try to sort the data, cells formatted as text will be treated separately from those containing numbers, so the cells won’t sort correctly. You’ll need to convert the cells to numbers first. To convert cells formatted as text to numbers:
Using a Single Formula to Fix Excel Not Sorting Numbers Correctly
If you’re not sure of the cause of your problems with Excel not sorting numbers correctly, you can combine all of the above methods into one formula. Hopefully, this should pick up any issues within your numbers, and allow you to sort your numbers properly again. To fix numbers that aren’t sorting properly in Excel with a combined formula:
Fixing Common Excel Problems
Knowing how to fix Excel not sorting numbers correctly can save you hours of frustration. However, even the steps above won’t work in all cases. If your cell contains a non-printable character that’s not removed by CLEAN, your data may still not sort properly. If this is the case, and nothing else works, use LEN to find the offending cells, and then simply delete the entire cell and type the value in manually. It may not be elegant, but it should work! Sorting problems aren’t the only problems in Excel. You might want to know how to fix problems with formula references, how to fix the arrow keys not working in Excel, or how to find a circular reference. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
