+ Reply to Thread
Results 1 to 15 of 15

The numbers in an alphanumeric string are not sorting correctly

  1. 07-18-2013, 05:25 PM #1
    sweetroman
    sweetroman is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    The numbers in an alphanumeric string are not sorting correctly

    Hi all,

    I'm attempting to sort my inventory Excel sheet of about 1,300 items. Currently, an A-Z sort is listing the order as (example) SR_BR1000, SR_BR1001, SR_BR100, SR_BR107, SR_BR1080, SR_BR110, SR_BR1102. I need it to be listed in actual numeric order--hundreds and then thousands.

    All items in the list start with either SR_ or OL_ and then have 1-2 letters, followed by 3-5 #s

    I've searched the web and feel like I've tried everything. Oh, and I'm using Excel 2007.

    Any help is greatly appreciated!!
    Lauren
    Reply With QuoteRegister To Reply
  2. 07-18-2013, 05:47 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    with those values in a2 down in b2 try
    =IF(ISNUMBER(MID(A2,5,1)+0),LEFT(A2,4)&TEXT(MID(A2,5,10)+0,"00000"),LEFT(A2,5)&TEXT(MID(A2,6,10),"00000"))
    fill down sort both by col b
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 07-18-2013, 05:58 PM #3
    sweetroman
    sweetroman is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: The numbers in an alphanumeric string are not sorting correctly

    Hmm not sure what that all means, but I did try it and it turned OL_BR100 into OL_BR00100 =/
    Reply With QuoteRegister To Reply
  4. 07-18-2013, 06:11 PM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    not when i do it
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-18-2013, 06:20 PM #5
    sweetroman
    sweetroman is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: The numbers in an alphanumeric string are not sorting correctly

    Not seeing my reply show up, so if it's there twice -- sorry!

    My system must be whack then, because the file you attached is showing:
    SR_BR01001
    SR_BR00100
    SR_BR00107
    SR_BR01080
    SR_BR00110
    SR_BR01102
    OL_BR00100
    Reply With QuoteRegister To Reply
  6. 07-18-2013, 06:28 PM #6
    sweetroman
    sweetroman is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: The numbers in an alphanumeric string are not sorting correctly

    OHH I get it-- sort by col B, then I can delete it. Don't have to keep those as my new #s. Derrr sorry

    That DOES work, however, I did forget to mention that some SKUs have color options so the very end will have "-BL" or "-GR" etc. Any way the formula can be tweaked for that? Thanks!!
    Reply With QuoteRegister To Reply
  7. 07-18-2013, 07:30 PM #7
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    wait for it
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 07-18-2013, 07:58 PM #8
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: The numbers in an alphanumeric string are not sorting correctly

    Here's a little shorter formula that will extract the number (as text) from character 6 to the number of numeric characters in the string. It'll work as long as there are no non-numeric characters mixed in after character 6.

    This is an array formula (enabled with Control+Shift+Enter when exiting cell edit mode)...

    Please Login or Register  to view this content.
    PS: If one wants to convert to actual number, wrap the formula in the VALUE() function.
    Last edited by jhren; 07-18-2013 at 08:02 PM.
    Reply With QuoteRegister To Reply
  9. 07-18-2013, 08:35 PM #9
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    Well non arraywould be
    LOOKUP(9.99E+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)))))
    but how does that help the sorting problem?
    having said that it gave me an idea
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by martindwilson; 07-18-2013 at 09:10 PM.
    Reply With QuoteRegister To Reply
  10. 07-18-2013, 10:34 PM #10
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: The numbers in an alphanumeric string are not sorting correctly

    Quote Originally Posted by martindwilson View Post
    ...but how does that help the sorting problem?...
    Only that its a shorter formula... still requires using as a helper column for sorting.

    If these are part codes, changing the number of numeric characters may prove detrimental
    Reply With QuoteRegister To Reply
  11. 07-18-2013, 11:10 PM #11
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    well humble pie time the array returns 0011 SR_BR0011_G from whereas the lookup only returns 11
    so new formula is
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    array entered with ctrl+shift+enter
    how to enter array formula
    http://office.microsoft.com/en-us/ex...872901033.aspx
    Attached Files Attached Files
    Last edited by martindwilson; 07-18-2013 at 11:14 PM.
    Reply With QuoteRegister To Reply
  12. 07-18-2013, 11:45 PM #12
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: The numbers in an alphanumeric string are not sorting correctly

    A shorter array formula which gives the same results...

    Please Login or Register  to view this content.
    Takes advantage of numeric text always starting at position 6 and not more than 5 characters.
    Reply With QuoteRegister To Reply
  13. 07-19-2013, 04:41 AM #13
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: The numbers in an alphanumeric string are not sorting correctly

    nice formula but unfortunately there may be times when it starts at pos. 4 
    Please Login or Register  to view this content.
    so
    SR_B107_ SR_B10007_m
    Reply With QuoteRegister To Reply
  14. 07-19-2013, 10:24 AM #14
    sweetroman
    sweetroman is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: The numbers in an alphanumeric string are not sorting correctly

    Whoa, I missed a lot! Tried the formulas and the one that seems to work is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I just use a separate tab as a "sorting sheet" and then copy the column of sorted SKUs back to the main tab.

    Thank you SOOOO much!!!!!! I would have never figured that out and it's been driving me nuts. You guys are amazing

    -Lauren
    Reply With QuoteRegister To Reply
  15. 07-19-2013, 10:46 AM #15
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: The numbers in an alphanumeric string are not sorting correctly

    Quote Originally Posted by martindwilson View Post
    nice formula but unfortunately there may be times when it starts at pos. 4
    All items in the list start with either SR_ or OL_ and then have 1-2 letters, followed by 3-5 #s
    so
    SR_B107_ SR_B10007_m
    Ahhh.... I overlooked the "1-2 letters", as all the examples had the first numeric character at position 6... so you really mean position 5.


    As Lauren is happy, I'll not pursue the matter further.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. The numbers in an alphanumeric string are not sorting correctly
    By sweetroman in forum Excel General
    Replies: 2
    Last Post: 07-18-2013, 06:18 PM
  2. Extracting Numbers from a Alphanumeric String
    By martinhardy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-01-2013, 11:02 AM
  3. Extracting Numbers from an Alphanumeric String
    By martinhardy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2013, 04:53 PM
  4. [SOLVED] Remove numbers from alphanumeric string. No standard format to the string.
    By ricunger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2012, 01:43 PM
  5. Sorting alphanumeric numbers
    By maurice.centner in forum Excel General
    Replies: 3
    Last Post: 05-05-2005, 09:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1