Closed Thread
Results 1 to 3 of 3

The numbers in an alphanumeric string are not sorting correctly

  1. #1
    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,928

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

    You need to extract the numerical part into another column, then you can use that column as the main sort field.

    Alternatively, you need to insert leading zeros into the numerical part to make them all the same number of digits long, e.g. SR_BR0107 (a better approach, but might conflict with other uses of the data).

    You will need to provide a larger sample of ID codes if you want a formula to assist in these tasks - attach a sample workbook.

    Hope this helps.

    Pete

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

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

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.
    HTH
    Regards, Jeff

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting Numbers from a Alphanumeric String
    By martinhardy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-01-2013, 11:02 AM
  2. Extracting Numbers from an Alphanumeric String
    By martinhardy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2013, 04:53 PM
  3. [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
  4. xtract text and numbers from alphanumeric string
    By amcriso in forum Excel General
    Replies: 1
    Last Post: 09-03-2012, 03:51 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1