Sorting Numbers and Text Together

November 22nd, 2010 by Leave a reply »

Last week, Elaine asked:

"I have project numbers that are both numeric and text which follow a parent and child relationship. The problem is when I sort by project number, all the numbers are sorted at the top, and all the text at the bottom. How do I sort, but keep parent projects with the child projects?"

So Elaine would like to see something like the table shown here – all projects sorted so that the numeric parent stays with the textual children.

.
The problem is that Excel doesn't sort this way by default. If I were to sort my project table in the standard 'Sort Ascending' fashion, I would get this result. Notice how all the numbers float to the top.

.

The fix for this kind of an issue is simple, but a bit annoying. Annoying because you're forced to create a helper column when all you want to do is sort.

.

Step 1:

Create a new column called SortKey. There, you will enter a formula to show all the values in the original column, but in a textual format.

In this case, I entered TEXT(A2, "###") in the first cell of my new column, then copied the formula down.


 

 .

Step 2:

Start a sort on your newly created SortKey column.


 

Excel will ask how you want the numbers treated during the sort. Select the option that says:

'Sort numbers and numbers stored at text separately'


 

 .

Step 3:

The final step is to delete or hide your SortKey column (depending on whether you'll need to continuously sort the data).

And there you have it.


.

Well Elaine, that's my answer.

If any of you gurus know an easier way to handle this kind of sorting, I would love to hear it.

Advertisement

16 comments

  1. TessaES says:

    If the project numbers are entered as text (and not left-aligned numbers), doesn’t Excel ask if you want to sort ‘anything that looks like a number as a number’ or ‘numbers and numbers entered as text seperately’ (mine does, version 2003)? With the latter option, the project numbers are sorted as desired

  2. datapig says:

    Tessa: You’re right, but these project numbers are coming from a database where the they are all stored in a text field. When they are output to Excel, Excel picks them up as a number automatically.

  3. TessaES says:

    I clearly should have read your post more carefully before commenting. I see now that that is exactly what your helper column does…

  4. TessaES says:

    So then the real problem is how to get Excel to properly import the field as being text, instead of trying to sort the mix of numbers and text afterwards.

  5. datapig says:

    Tessa: I’ve done this before. In the output query on the database side, I prefixed each value with the single apostrophe (‘).

    This turns 546789 into ’546789.

    When this outputs, Excel recognizes it as text.

  6. EricK says:

    Slightly quicker and easier is to use a formula like
    =”a”&a2
    in the helper column.

    Not only is this fewer characters to type(!), but the sorting should work without the intermediate “Sort Warning” coming up, and can in fact be achieved simply by selecting cell c2 and pressing the sort button in the toolbar/ribbon.

  7. chrisham says:

    I think a much simpler way around would be to run a Text to Column and convert everything to Text, then Sort it and finally run Text to Column and reverse the Numbers back to the General format. The Alpha-numerals will remain text of course….

  8. Petter says:

    I agree with chrisham. Text to Column is to prefer. In step 3 you have to choose “Text”.

  9. trina says:

    This fixes a similar problem I have except that my numbers range from 3 digits (102) to 5 digits (1012) and when sorted using the sortkey column, it places 102 down under 1012. Anyone know how I can correct this and keep the previous fix intact?

  10. Javier says:

    Trina,
    I think you need to create a helper column with “entire” number. That is, to convert “102″ to “0102″.
    You can use a simple formula in the columna like =TEXT(A2; “0###”), that convert any number in a 4 digits form.

  11. Luther says:

    I know this an old post but I’m trying to solve a problem in sorting. What about if your trying to sort with decimals as well? For instance, how can you fix it so that 1.01.c1 doesn’t appear right above 10.01.c1. In other words, I want 2.01.c1 to come after 1.01.c1 and 10.01.c1 to come after 9.01.c1 as normal.

  12. Venus says:

    I have 5 digit numbers, then I have numbers with letters and hyphens, eg, 56750 or 56-750-T-01
    I’ve tried every suggestion here, but the sort remains, numbers at top then numbers with letters and hyphens.

    Does anybody have a clue as to what I’m doing wrong, or am I just stuck because of the way our numbers are formatted?

  13. NAT says:

    I tried this formula and many others but I’m still not getting the correct sort. My sort column is as follows:
    A 1-4
    A 5-6
    A 7
    A 8-13
    A 14-15 etc…
    but this is what I’m getting
    A 1-4
    A 14-15
    A 16-17
    A 18-19
    A 20-28
    A 29-33
    I am not sure what else can be done. Any suggestions would be greatly appreciated.

  14. datapig says:

    Nat: What is the sort you’re trying to achieve? Looks like it’s sorting.

  15. Bob says:

    I have similar problem with Race vehicles.

    1
    1a
    1t
    2
    3
    4h
    ============
    When using excel it places all the numeric san text at the top of the list, then scrambles the numeric text at the bottom of the list. They are all in one single column. Formula to fix this and how in Excel for Mac 2011?

  16. LukaM says:

    It does not work in case of number part with different digit lenght i.e.:

    1
    1a
    2
    2a
    2b
    12
    12a
    111
    123c
    123

    FYI it worked well without any effort in 2003
    Why do they add makeup features like “sort by color”, when they do not know users basic every day needs?

Leave a Reply