Sorting values in Excel issue

Question

I have a spreadsheet that I am sorting based on an "item number" column. So if the item number column has the following values:

2.2
2.1.b

I want the row with the 2.1.b to be before the 2.2 row. However, the sort function in Excel puts 2.2 ahead. It must determine value based on the # of characters.

Is there a way to do sort the column in VBA?

Answer 1

I would add a hidden column with a formula to convert the 2.1.b format to a simpler, sortable numeric value, like 10000*(first digit) + 100*(second digit) + (ascii(letter) - 'a'), where: first digit = 10000*NUM(SUBSTRING(A1;FIND(".", A1))... etc.

Answer 2

This can easily be done in Excel by creating a "Custom List" and then sorting on that list.

Custom Lists allow you to create your own default sort order. You can create a new Custom List as follows "0,1,2,3,4,5,6,7,8,9,.,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z". You can add to this if your sorting needs further logic.

This new list will then sort according to numbers first, the a period, then a letter.

You create the list, when sorting for the first time select "Custom Sort", choose your new list, and you're done. Now when you sort, the data will be sorted according to your new criteria.

Answer 3

You do not need to use code to do this--Excel supports this built-in:

1. Format the entire column's "numeric format" as Text, not General.
2. You will have to reenter any number-only entries that were previously entered.

As an alternative to step 2 (if you already have a lot of preexisting entries), you can use the TEXT function and either sort on that column or copy-and-paste-values to overwrite your original column values.

I tested this in Excel 2007, but I'm pretty sure this behavior has been around for awhile.

Answer 4

If you prefix each value with an apostrophe, this will indicate to Excel that the value is text rather than a number.

At least that's the theory.

Excel is pretty agressive about trying to fix text that looks like numbers. It puts a notification on each cell that there is a number formatted as text ("duh!"), and then you have to select "Ignore error" (there is no option for "It's not really a number").

Then change the Number Format from "General" to "Text" just for good measure.

Then when you try to sort it, it will, once-again, try to hold your hand and fix your "mistakes". By choosing "Sort numbers and numbers stored as text separately" you can make it do what you want. (There is no "It's all [adjectival explicative] text, [interjective explicative]!" option, sadly.)