Show me how Excel can sort numbers and text in the order I want

April 17, 2008 12:26 PM

Any Excel gurus in the room? I have an Excel spreadsheet with over 800 rows of data. Each row begins with a number (like 0001), and some have letters after the numbers (like 0001a). The Sort function doesn’t sort the way I’d like it to.

Each row begins with a number, which is sequenced as follows...
0001
0001a
0001b
0002
0003a
0004

...you get the idea. Not every number gets a letter, but some do. The spreadsheet relies on continuous use of AutoFilters and sorting options to retrieve the data I need. However, “Sort Ascending” always changes the order of these numbers into an undesirable result. It sorts numbers first, and then numbers-with-letters, as follows:

0001
0002
0004
0001a
0001b
0003a

That’s bad. It’s cumbersome to find and sort the data. I can’t seem to make this work the way I want it to. I’ve tried using “Format Cells” to specify the cells are text, not numbers. I’ve also read through this support document from Microsoft, and tried the solutions there, but the same thing continues to happen. Am I doing something wrong?

If anybody has had experience resolving this problem before, I’d love to hear about your method and solution!

posted by Milkman Dan to computers & internet (7 comments total)

It will sort correctly if the cells actually contain strings, but selecting the cells and applying "text" format won't do it.

Make a new column beside your existing labels and beside each one (say the first one's in cell A1 and your new column is B), put

=text(A1,"00000")

Now you'll have actual strings that you can copy and paste-as-values back over the originals (if they look like what you want!) and it should sort like you want.
posted by Wolfdog at 12:38 PM on April 17 [1 favorite]

(Rather just "0000" for your numbers, actually)
posted by Wolfdog at 12:40 PM on April 17

A different kludgy solution (Excel 97)-

Parse the first column into two, separating the data between numbers and letters. Then sort by both columns.

First, insert a blank column to the right of the first column. From the Data menu, select "Text to Columns". Choose the Fixed Width radio button, and hit Next. Drag the slider to the fourth position and select the Finish button.

You *should* now have 2 columns - one with numbers, and one with letters. When you sort on Column A first, then Column B second, you should see the desired results.

HEY Wolfdog!
posted by grateful at 12:40 PM on April 17

Simply changing the column format to "Text" doesn't actually change the existing values to Text. You'll notice that Microsoft KB suggests retyping each value in to make the actual conversion which is of course, a ridiculous amount of work.

Instead, do the following:
1) Create a new column to the right of your sorting column. Set its Format to "Text".
2) Assuming your first record is in A2, enter the formula =CONCATENATE("",A2) into cell B2.
3) Copy that formula down column B to the last record.
4) Highlight the entire column, copy it, and do Edit > Paste Special > Values to copy it on top of itself.
5) Now you can delete column A and use the new column for sorting.
posted by junesix at 12:53 PM on April 17 [1 favorite]

Wolfdog almost got it right.

1) Insert a column next to your ID number. We'll call it B.
2) In the first cell of the new column, type:
=text(A2,"#")
3) Copy that all the way down.
4) Sort by column B.
posted by darksasami at 12:56 PM on April 17 [1 favorite]

Assuming you want to retain the leading zeroes, Wolfdog is right. darksasami's method will strip the leading zeroes from the ones that are purely numbers, but not from the ones that are number/letter mixes. And whether you retain the leading zeroes or not will make a difference to the sort order once you sort them. For example, with the same set of initial data, Wolfdog's method would give you:

0001
0001a
0003
0013

Whereas darksasami's would give you:

0001a
1
13
3
posted by DevilsAdvocate at 1:21 PM on April 17

Well, after about an hour of playing around with your (brilliant and helpful) suggestions in a dummy file, I re-opened my original and discovered and my co-worker had already solved the problem, without any of our help. Here's what she did:

1. Select the whole sheet.
2. Click Data > Sort.
3. Under "Sort By," click Column A
4. Under "My data range has," select "Header rows." Press OK.

A message then appears, stating: "The following sort key may not sort as expected because it contains some numbers formatted as text: [Column A's name]. What would you like to do?"

5. Select option 2, which is "Sort numbers and numbers stored as text separately."

Bam. It was that easy.
posted by Milkman Dan at 2:13 PM on April 17