How-To

How to Fix Excel Not Sorting Numbers Correctly

By Adam Davidson

Updated November 9, 2023

Are you having problems with Excel not sorting numbers correctly? If so, here are a few fixes that you should try.

When you copy data in Excel from external sources, you may also copy additional characters that don’t display in your cells but are still hiding there, waiting to cause you problems. 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 includes these characters, you may see 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.

1. Click on an empty cell in the same row as the first cell of the data you want to check.
2. Type =LEN(
   
   
3. Select the first cell in your data.
   
4. Type ) and press Enter.
   
5. The formula will calculate the number of characters in your cell.
6. To apply the formula to all of your data, click and hold the drag handle in the bottom right-hand corner of the cell.
   
7. Drag down to the bottom of your data.
   
8. Release the cursor, and the formula will autofill.
9. Compare each value to the number of digits you can see in the corresponding cell.
10. If the result of your formula is higher than the number of digits in your cell, you know that there are additional characters in the cell that are not visible. In the below example, the cell is showing a single digit, but LEN tells us there are two characters in the cell.
    
11. Follow the steps in the sections below to remove them.

How to 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.

1. Find an empty cell in the same row as the first entry of your data.
2. Type =CLEAN(
   
   
3. Click the first cell of your data.
   
4. Type ) and press Enter.
   
5. Your new cell should now only contain a number, with any non-printable characters stripped away.
6. To apply the formula to all of your cells, click and hold the drag handle in the bottom-right corner of the cell.
   
7. Drag downwards over all of the cells where you want the formula to appear.
8. All of these cells should now have the non-printable characters removed.
   
9. You can check that the additional characters have been removed by using the LEN function, as explained above.
10. Copy your newly created column.
11. Right-click in the first cell of the original data and select Paste Special > Paste Values.
    
12. You can now delete the column you created to clean your cells.
13. Try sorting your data again to see if this has fixed things.
14. If the data still does not sort correctly, then it’s likely that some of your numbers are still formatted as text. Check out the section below to fix this.

Remove Spaces from Cells

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, empty spaces are the most common non-printing character in Excel spreadsheets. They are ASCII code 32, so they aren’t removed by the CLEAN function. To get rid of extra spaces, we need to use the TRIM function.

How to Remove Spaces from Numbers using the Excel 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(
   
   
3. Select the top cell of your data.
   
4. Type ) and press Enter.
   
5. Any empty spaces should now be deleted from the cell.
6. Apply the formula to the remaining cells by clicking and holding the drag handle in the bottom-right corner of the cell.
   
7. Drag downwards over all of the cells that you want to copy the formula to.
8. All of your cells should now have had the spaces removed.
   
9. Copy these new cells.
10. Right-click in the top cell of your original data and select Paste Special > Paste Values.
    
11. You can delete your new column as you’ll no longer need it.
12. Try sorting your data again. If it still doesn’t work, some cells may be formatted as text, so you’ll need to try the steps below.

How to 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.

1. If you’ve followed the steps above, and some of the cells have small green triangles in the corner of the cells, this indicates an Excel error.
   
2. Highlight all of the cells, and a Warning symbol will appear.
   
3. Click the Warning symbol and select Convert to Number.
   
4. If you don’t see any green triangles in your cells, type 1 in an empty cell and then copy that cell.
   
5. Highlight all your data, then right-click and select Paste Special.
   
6. Select Multiply and click OK.
   
7. Your cells should now be numbers.

How to Use 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.

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 hold the drag handle of your cell.
   
6. Drag down over the remaining cells.
7. Let go, and the other cells will autofill.
   
8. Copy the cells, then right-click the first cell of your original data.
9. Select Paste Special > Paste Values.
   
10. Try sorting your new cells; they should now sort as expected.

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. I’ve written several articles that talk about how to solve problems with formula references, how to fix the arrow keys not working in Excel, or how to find a circular reference. If you still need help, feel free to post your issue in the Excel section of our free community forum.