Sorting Issue in SharePoint: File Order Problem

Question

I came across an issue in SharePoint where my files are listed as follows:

However, I want them to be listed as:

• A1
• A2
• A3
• A4
• A5
• A6
• A7
• A8
• A9
• A10

I read a solution from Microsoft about using sorting to modify a SharePoint view, which mentions that I can fix this situation by changing the underlying type setting for the column.

However, I have a problem with step one, as I could not find the "Edit" option under Column Settings. Can anyone help and show me where it is and how I can solve this issue? If possible, could you also provide pictures for guidance, please?

Additionally, I saw people mention that I could create a second column that just contains the number, keep it hidden, and use it to sort. However, when I sort them from smallest to largest, folders with multiple files do not sort according to the number I've provided, and the overall view is not what I wanted. If I can use this method, how can I put these folders in order?

I want the library (the overall) to sort in ascending order;

however, one of the folders has (A10), which prevents it from sorting correctly. After I apply the new 'number' sorting, the library does not remain in ascending order.

Any help would be appreciated!

Answer 1

Dear MingSin,

Good day! Thank you for reaching out to the Microsoft Community. We are here to assist you.

I appreciate you way of troubleshooting. This happens because SharePoint treats the numbers as text, leading to an alphabetical sort order (e.g., 1, 10, 2, 3).

You can try using a calculated column to ensure the numbers are sorted correctly. Here's how you can do it:

Step-by-Step Solution

1. Create a Calculated Column:

• Go to your SharePoint list.
• Click on the settings gear icon and select "List settings."
• Under the "Columns" section, click on "Create column."
• Name the column (e.g., "SortOrder").
• Select "Calculated (calculation based on other columns)" as the column type.
• In the formula box, enter a formula that pads the numbers with leading zeros. For example, if your numbers range from 1 to 100, you can use the formula:

=TEXT([YourNumberColumn], "000")

• Set the data type returned by this formula to "Single line of text."

1. Sort by the Calculated Column:

• Go to the view where you want to sort the items.
• Click on the settings gear icon and select "Edit current view."
• In the "Sort" section, select the calculated column (e.g., "SortOrder") and choose "Ascending."
• Save the view.

Example

If your original column contains the numbers 1, 2, 10, 3, the calculated column will convert them to 001, 002, 010, 003, ensuring they are sorted correctly as 1, 2, 3, 10.

As you have folders with multiple files, make sure to apply the same sorting logic within each folder also ensure that the calculated column is hidden from the view if you don't want it to be displayed.

If above steps doesn’t help, you might need to escalate the case to Microsoft support team. Unfortunately, community members don’t have access to remote support tool. This can only be initiated by an Office 365 Global Administrator. The Microsoft Support Team has higher permission to remotely assist you and collect background logs for the root cause. This is a more efficient way in handling this case for you.

If you are an admin, I recommend you get support by following steps from this link: Get support - Microsoft 365 admin | Microsoft Learn.  If you are not an admin, you can contact your admin to contact Microsoft support. You can look for the way to find your admin through this link: How do I find my Microsoft 365 admin? - Microsoft Support.

---

Please accept my apologies for redirecting you to the related team support, as the moderators in this community have limited resources and permission to verify the backend information and to get the quick and better assistance, we requested for this.

I hope you are staying safe and well! Have a great day and take care! 😊 

Sincerely, 

Naren | Microsoft Community Moderator

Answer 2

Thank you so much for your prompt replies. As I am trying to use your solution, I encountered an issue.

When I try to select "Calculated (calculation based on other columns)" as the column type, this option is not available to me. Instead, they only show the options below:

I also tried selecting "Single line of text" or "Number" as the column type and entering the formula: =TEXT([YourNumberColumn], "000"). However, I am informed that the formula has a spelling mistake or that the column does not exist. In another scenario, it says the validation formula can refer to this column and not others.

Could you please help me resolve these issues? Thank you so much !

Answer 3

Dear MingSin,

Thanks for posting back.

It sounds like you're not entering the column names in your formula,  "YourNumberColumn" in the formula is a placeholder for the actual name of the column in your SharePoint list that contains the numbers you want to sort. You need to replace "YourNumberColumn" with the exact name of your column.

For example, if your column is named "OrderNumber," the formula would be: =TEXT([OrderNumber], "000")

Make sure to use the exact column name, including any spaces or special characters, and enclose it in square brackets. If your column name is "Order Number," the formula would be: =TEXT([Order Number], "000")

For validation formula can refer to this column and not others you can try unchecking the box for .

If you still encounter issues, please double-check the column name and ensure it matches exactly as it appears in your SharePoint list.

I hope you are staying safe and well! Have a great day and take care! 😊 

Sincerely, 

Naren | Microsoft Community Moderator