In Microsoft Excel, we store data randomly. Then we process that data according to our needs and get the desired result. Excel has some built-in features according to our need to process data. One of them is to sort data. But, sometimes we face problems while sorting a bunch of numbers. We will discuss how to solve the problem of numbers not sorting correctly by Excel.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## 4 Reasons and Solutions with Excel Not Sorting Numbers Correctly

There are several reasons why **sorting numbers** does not work correctly in **Excel**. Specifically, weâ€™ve found **4** reasons.

- Non-printable characters in numeric data
- Leading or trailing spaces inside the data
- Numbers are accidentally formatted as Text
- Numbers generated using
**RAND, RANDARRAY,**or**RANDBETWEEN**functions

In the sections below, we will discuss these reasons, how to identify them, and their solutions.

**Reason 1: Numbers Have Non-printable Characters**

Letâ€™s have a look at the following dataset.Â It has some product price data which are collected from the internet. We have copied them from the internet and formatted the prices in Excel Accounting format. (Though the first 4 entries are not formatted accordingly due to a so far unknown issue.

Now, letâ€™s try to sort the data.

- First, select all the cells of the
**Price**column. - Press the right button of the mouse. Choose the
**Sort**option from the**Context Menu**. - Select the
**Sort A to Z**option.

Now, look at the result.

Sorting is not performed successfully here. The bottom **4** cells are sorted in the wrong manner

**How to Detect the Issue**

Now, we need to find out how many non-printable characters are added to our desired data. We will use the Excel** LEN function** to find out the number of objects in each cell. Press **Ctrl+Z** to undo the previously applied sort operation.

- Add a column named
**No. of Char**. - Go to
**Cell D5**and put the following formula.

`=LEN(C5)`

- Now, press the
**Enter**button and drag the**Fill Handle**icon downwards.

In the new column, the no. of the character of each cell has shown. In **Cell D6 **and **D11, **we have **2.Â **That means their corresponding cells **C6** and **C11** contain **2** characters. But we can see only a single numeric character there. So, there is **One (1)**, the numeric character at cells **C6** and **C11**.

**Solution: Remove the Non-printable Characters with CLEAN Function**

To sort the whole data successfully, we need to remove those non-printable characters. The Excel **CLEAN function** easily removes those non-printable characters.

- Add a new column named
**Verified Data**on the dataset. - Now, go to
**Cell E5**and paste the formula below.

`=CLEAN(C5)`

- Press
**Enter**and pull the**Fill Handle**icon towards the last cell.

- Now copy the numbers from the Cleaned Data column, click on Cell E5 and type
**ALT+H+V+V**to paste them as values. - Then click on the
**Error icon**and select the**Convert to Number**option.

- Now, perform the sort operation as shown previously.

After removing all the non-printable characters, data has been sorted successfully.

**Read More:** **How to Sort in Excel by Number of Characters (2 Methods)**

**Reason 2: Presence of Leading or Trailing Spaces**

If the numbers contain leading or trailing spaces in them, then you will face sorting issues with such numbers. To understand clearly, letâ€™s see the following example.

Assume that we are working with the following dataset. We have targeted to sort the price of the items from the list.

Notice that all data are not right-aligned in the **Price **column.

Now, we will try to sort the data in the **Price **column from the smallest to the largest.

Here, the last 3 cells are not sorted accordingly.

**How to Detect the Issue**

If your numeric data is mixed with spaces in them, they are not actually numbers anymore. So you can check whether they are numeric values or not if you fail to sort them properly.

We can use the Excel **ISNUMBER function** to perform this checking. Just follow the steps below.

- First, add a column named
**Status**. - Then put the following formula on
**Cell D5**.

`=ISNUMBER(C5)`

- Now, press the
**Enter**button and expand to the rest of the cells of that column.

We get **TRUE** if the object is a number otherwise, we will get **FALSE**. The last **3** objects of the **Price **column contain data; those are not pure numbers. There are leading and trailing spaces with the data.

**Solution: Get Rid of the Spaces with TRIM Function**

As the **TRIM function** removes extra spaces from a given set of Excel data, we can use it for our cause too.

**Steps:**

- Add another column named
**Modified Data**. - Go to
**Cell E5**and put the following formula.

`=TRIM(C5)`

- Now, press the
**Enter**button and pull towards the last cell.

- Now, sort the dataset from smallest to largest.

We successfully performed sorting.

**Read More:** **How to Put Numbers in Numerical Order in Excel (6 Methods)**

**Similar Readings**

**How to Sort by Month in Excel (4 Methods)****How to Sort IP Address in Excel (6 Methods)****[Solved!] Excel Sort Not Working (2 Solutions)****How to Add Sort Button in Excel (7 Methods)****How to Sort Unique List in Excel (10 Useful Methods)**

**Reason 3: Numeric Values Accidentally Formatted as Text and Not Being Sorted Properly**

We get another interesting problem here. In our dataset, we can see all our numbers in the **Price **column. But, some of the cells contain data that are not in **Number** format but are in **Text **format. Here is the dataset.

- Now, sort the data from the smallest to the largest to see what happens after the operation.

We can see the last **4** cells remain unsorted.

**How to Detect the Issue**

We need to find out whether they are numbers or not first. The **ISNUMBER function**Â is used for this.

- Go to Cell D5 in the
**Status**column. - Put the formula below.

`=ISNUMBER(C5)`

- Now, press the
**Enter**button and extend that.

We can see that **FALSE**Â in the last **4** cells. That means those are not numbers. We need to get the numeric value of those text data.

**Solution 1: Convert Text to Numeric Data with VALUE Function**

We will use the** VALUE function** here. This **VALUE **function will extract the numeric value from a text reference.

- Go to
**Cell E5**and paste the following formula.

`=VALUE(C5)`

- Now, press the
**Enter**button and pull to the last cell.

We get numeric values from the text data.

- Now, perform the sort operation from the smallest to the largest.

Now, the data are sorted correctly.

**Solution 2: Convert Text into Number Using a Default Button**

We have an alternative solution to this problem. That is to convert the text values into numbers and sort them.

- Press
**Cell C11**which contains a text value. - A warning button will show with multiple options.
- Choose the
**Convert to Number**option.

- Do this for other cells that contain text values.

- Now, we have converted all the values to numbers. So, sort the number from smallest to largest.

We get the sorted result here.

**Read More:** **How to Use Excel Shortcut to Sort Data (7 Easy Ways)**

**Reason 4: Numbers Generated with Excel RAND or RANDBETWEEN Functions**

Sometimes we generate random numbers in Excel usingÂ **RAND** or **RANDBETWEEN **functions. One issue lies with such numbers- whenever you perform an operation within the range of cells that have random numbers, the numbers frequently change. So when you try to sort such random numbers, it will give you the proper results.

**Solution: Copy the Generated Numbers and Paste Them into the Same Place**

First, we will convert the data into fixed values and then perform the sort operation.

- Select all the cells of the
**Age**column. - Copy them by pressing
**Ctrl+C**. - Now, press the right button of the mouse.
- Select
**Values(V)**from the**Context Menu**.

- Here, we get fixed values. They will not behave like random data from now.

- Now, sort the
**Age**data from the smallest to the largest.

**Read More:** **How to Use Sort Function in Excel VBA (8 Suitable Examples)**

**Conclusion**

In this article, weâ€™ve shown which problems we face when performing sort operations. After detecting the problem weâ€™ve shown their solutions too. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

**Related Articles**

**Auto Sort When Data Is Entered in Excel (3 Methods)****How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)****Sorting Columns in Excel While Keeping Rows Together****How to Sort by Color in Excel (4 Criteria)****Random Sort in Excel (Formulas + VBA)****How to Sort Duplicates in Excel (Columns and Rows)**

Or u just copy/save all data to csv format sheet – open it in LibreOffice and do the magic one 1 click only the right way. Then u copy values back to crappy excel (if u have the need) ! Without any “vacuum cleaner” twist and quirks ms engineer requirements for one click function that should work out of the box. This only shows how ms has ideas how excel had to become not so 1 click function sheet. Time to ditch it.