Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
I have a column of integers in my spreadsheet, and the column is formatted as a number. Each cell inside the column is formatted as a number.
But I can't seem to sort them in numerical order - you know, 1,2...9,10,11...
Instead, it always sorts them according to text sorting : 10, 100, 101, 102...11, 111, 112, ....
How can I sort my column of numbers by number instead of by text?
It sounds as if your numbers are being interpreted as text by Excel. A good indication of this is that they automatically align to the left of each cell. Numbers normally are aligned to the right.
If this is the case, a simple fix is to move into a cell, press F2 then Enter. Repeat for each cell containing the numbers that don't sort. This works by putting the cell into edit mode and then out again. If the contents can be interpreted as a number then Excel converts it to a number.
If you have too many cells to do this manually, you can use the VALUE function to create another column contain true numeric values and then paste them (by value) back over the offending cells.
I know this is an old post, but since it's at the top of the search results for this kind of thing...
There's a much easier way to do this. To convert numbers stored as text to numbers:
And presto! Your column is now formatted as actual numbers and you should be able to sort smallest to largest instead of A to Z.
You can do this on the data directly in a single shot by without creating working columns. My favoured method is using Paste Special (I normal multiply by the "text" by 1)
Debra Dalgleish provides 7 methods here, http://www.contextures.com/xlDataEntry03.html
The Paste Special method as listed by Debra is
Just change your single digits (1,2,3,etc.) to 01, 02, 03. Then sort A to Z.
I did all of them . But did not help.. My solution was just multiply it with 1 to the next cell. Copy and special past multipled cell to next cell. delete old 2 cells
I am new at this but I just figured out something, maybe it will help.
I was sorting something on my Excel file which has number of days and I needed to sort them by days.
This sort the column in sequence like 1, 100, 102, 2, 201, 203, 3, 205, ...
The solution: using Sort and Filters.
Format the cells to make sure that they are all numbers. If they have prefix or suffix make sure you remove them as well. In my case I had "days" as suffixes. To remove it you can do a =LEFT(A1, LEN(A1)- number of letters you want to remove), change A1 to appropriate cell.
Highlight all the cells in the column and format the cells to be a number.
When you do a filter it should show as a number filters instead of a text filter.
Filter it by value of number instead of A-Z.
Change the format of the cell to =TEXT(A1,"#0"), this will arrange it properly.
Maybe this helps: I had the same problem and nothing worked. The solution was extremely simple!
My numbers had spaces in between. Thus it was not recognized as numbers but text. I followed this golden tip:
The easiest way to get rid of excess spaces is by using the standard Excel Find & Replace option:
Press Ctrl + Space to select all cells in a column. Press Ctrl + H to open the "Find & Replace" dialogue box. Press Space bar in the Find What field and make sure the "Replace with" field is empty. Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.
After I did this, all numbers were aligned on the right side and I could sort it as I wanted it to. Hope it helps!
Maybe this function is added to the new excel, but you could create a new column and use =NUMBERVALUE(A1), with A1 being the old column. This convert the text into number and then you can sort it easily.
For people who want to extract the mixture of string+number, you could use MID() to extract the number first.
As someone else already noted this is an old post, but I also found this while doing a search for this problem. For me the problem was the of the '<' symbol. Once I removed this from the cells containing this, all was well again :-)
Long live the internet!
Why not another point?
Excel has a way of handling this without the question of converting and formatting and how these aren't necessarily the same thing and how other characters (as mentioned: thank you internet) might interfere. Mind you, it won't fix the latter, but if it fails, then you KNOW it's the latter so troubleshooting moves on to where it will work, not to dead ends.
When sorting, and this kind of thing arises, Excel offers an opportunity for your sort to treat text that looks like numbers AS numbers. Or not to do so. If you choose the former, your sort will work as desired when it is a matter of numerical characters that Excel is (still) treating as text characters.
As to the misunderstandings some show above, reformatting text formatted cells as some numerical format, so "numerical" instead does not always (OFTEN does not always) actually do the "conversion"... Yes, the formatting is changed. No, Excel is still not looking at them as numbers. Forcing the actual change in how Excel regards them is a mild form of something Excel folks call COERCING. Some of the ways you can do that are mentioned above: actually edit each cell (not making a typed change, just entering Edit mode with, say F2, or your mouse) and pressing Enter will do it, using Paste|Special|Multiply multiplying by 1 or the P|S|Add version adding 0 (though that has pitfalls if the content really is "text"), using Text to Columns... all of these are meant to coerce the format to be recognized by Excel. All should work, nicely, and if they don't, then the reason is almost certainly that there are non-visible, or barely visible and so not noticed, characters between one or more pairs of digits. Website material is notorious for this. Excel has a (primitive) help for that, the CLEAN() function, and the internet offers various (short and old and kinda poor) lists of other characters, ones CLEAN() does not know about, that one can hunt for. All may be just what you needed, but usually, not completely.
Nowadays (2021) with SPILL functionality, we can do things like test each character in a cell, keep it if numerical (of course, problematic for decimal characters, negative markers, and so on... but still...), drop it if not, and make a string of the keepers, then convert it with VALUE(). Works for almost anything, and since the characters that cause problems are NOT in the series of characters that include decimal markers and numbers, one can just check against a list including them and a few others (that don't matter so don't cause a problem) and only end up foiled by text like [28.335_]- (weird formats from when they were text formatted, and hence may still (may not) remain after reformatting and trying the coercion methods.
Anyhow, just an answer trying to show what of the previous ones was related and how they relate to the overall issue. Everyone here seemed to be part of one of those "10 blind men describe an elephant (whilst a joker actually switched in a gorilla before they started)" stories. Solving the problem one has when this symptom jumps you requires knowing the whole sitch so one can go down some workable path, not the many paths that are not precisely wrong, but still lead to no solution at all. Like reading a medical book, a page here, a page there, then trying to fix someone's flu by setting his leg.
I have the same problem but finally resolve it after a couple of tries and failures. The best solution I found when sorting numbers in a database that contains number like 1,2,3...10,11,12,13....NC,CAR, 4-A,4-B...etc. Ordinarily if your database contains numbers like above example, if you sort them out it will sort like this 1,10,11,2,3,5,6,7,8,9,4-A,4-B...etc whatever the combination of alpha-numeric number...the solution is simple. First be sure to point your mouse and select the column you want to sort out then.. Create a custom list...in the Home tab of MS Excel, click Sort & Filter, choose Custom Sort, you will see 3 options on how your sort will come about(Column, Sort On, Order),choose Order drop down menu, set custom lists, add or input/type your sorting format in the lists entries, then voila there it is, a custom sort format of your desire. The next time to sort your database just use the custom list you have put. I am using an MS Excel 2010 version. Hope this helps to fellow soldiers battling problems in the world of the spreadsheet.:)
