7

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?

3
  • This question exists, superuser.com/questions/218012/… , however, it deals with pivot tables and the answer is to create another column with numbers. But the column in my spreadsheet is already converted to number format. – rlb.usa Oct 4 '11 at 21:02
  • What version of excel, this worked for me in Excel 2007 – Nick Josevski Oct 4 '11 at 21:51
  • It is Excel 2010 – rlb.usa Oct 4 '11 at 22:04

10 Answers 10

8

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.

1
  • 1
    There is another catch, checkbox that says 'My data has headers' which has to be unchecked before sorting – Xinus Jan 6 '13 at 11:48
4

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:

  1. Select the whole column.
  2. On the Data tab, click the Text to Columns button.
  3. On the wizard that opens, just go ahead and click Finish. You don't need to go through the extra steps since they're for if you want to split columns.

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.

2

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

  • Select a blank cell
  • Choose Edit > Copy
  • Select the cells that contain the numbers
  • Choose Edit > Paste Special
  • Select Add
  • Click OK
  • To apply number formatting, choose Format > Cells
  • On the Number tab, select the appropriate format, then click OK
3
  • Doesn't help, they are already formatted as numbers – rlb.usa Oct 4 '11 at 22:01
  • 1
    Although Pasting and then using one of the paste options that comes up to paste as a number does work – rlb.usa Oct 4 '11 at 22:07
  • Yes, its the Paste Special operation that forces the convert. Glad to hear you got it working. – brettdj Oct 4 '11 at 22:15
1

Just change your single digits (1,2,3,etc.) to 01, 02, 03. Then sort A to Z.

1
  • 1
    Welcome to Super User! I know you are new to this site, but wanted to make you aware you are posting an answer to a 4-year old question that has an excepted answer already. There is nothing wrong with doing so, just realize it may not be responded to or accepted. – CharlieRB Jun 30 '15 at 18:31
1

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

0

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.

  1. 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.

  2. Highlight all the cells in the column and format the cells to be a number.

  3. When you do a filter it should show as a number filters instead of a text filter.

  4. Filter it by value of number instead of A-Z.

Click here to see image of Number Filters

0

Change the format of the cell to =TEXT(A1,"#0"), this will arrange it properly.

2
  • This seems to be the opposite of what the question asks. – RalfFriedl Oct 11 '18 at 7:21
  • Welcome to Superuser:- Your answer can be useful to other readers that the accepted answer did not work for but requires detail and a working answer to the question. Please take a couple of minutes and read:- help center .Answering: How to Answer, again welcome to superuser and i hope you keep coming back.Thankyou – mic84 Oct 11 '18 at 9:14
0

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!

0

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.

-1

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.:)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.