+ Reply to Thread
Results 1 to 3 of 3

Excel not sorting numbers correctly

  1. #1
    Registered User
    Join Date
    04-03-2012
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    3

    Excel not sorting numbers correctly

    hi guys,

    i am average excel user and am having an issue.
    I have some issues sorting a column.

    Attached is a two sheet workbook:

    on the first sheet is 5 columns (store #, Street, City, State, Zip)
    on the second sheet i used this entry:
    =Sheet1!A2 & " : " & Sheet1!B2 & ", " & Sheet1!C2 & ", " & Sheet1!D2 & " " & Sheet1!E2
    (The attachment is set up as above)


    When i then try and sort the sheet 2 column, it arranges the numbers very strangely, where 100 comes before 11, 23, and 7 for example.
    The reason im concerned about the sorting is that i want to use the new column on sheet 2 in a pivot table that has other columns with store information.
    When I create the pivot table and put the sheet 2 column in as a row label, it does the weird sorting.

    I hope i am expressing my issue clearly. I would like it to sort in standard numerical order (1,2,3,...,9,10,11,...,98,98,100,...,199,200,201,...,etc)

    I'd appreciate any help or suggestions
    Attached Files Attached Files
    Last edited by lpexcel; 04-03-2012 at 05:30 PM. Reason: Poor Title

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Need help with sorting issue

    The issue is because your function that joins all of those cells together creates a string. When numbers are part of strings, they are sorted as strings. If two strings start with A, the sort procedure looks at the second character. If one has A (aardvark) and the other has B (absolute), then it will sort that way. Same thing is happening with the "numbers."

    True numbers (those that Excel recognizes as numbers) will sort properly.

    The strings "100" and "11" both start with "1", so it looks at the second character. "0" is less than "1", so it is sorted first. If you want to sort by the store number on the new sheet, the easiest thing to do would be to create a helper column with the store numbers and sort using that column.

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Excel not sorting numbers correctly

    Try changing your formula to:

    =TEXT(Sheet1!A2,"00#") & " : " & Sheet1!B2 & ", " & Sheet1!C2 & ", " & Sheet1!D2 & " " & Sheet1!E2

    add another 0 before the # if you have more than 999 stores

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1