Find solution
How can I sort my data in alphabetic order?

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

  • 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 http://support.microsoft.com/kb/322067, 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!

  • Answer:

    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.

Milkman Dan at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

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.

Wolfdog

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.

darksasami

(Rather just "0000" for your numbers, actually)

Wolfdog

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!

grateful

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

DevilsAdvocate

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.

Milkman Dan

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.