+ Reply to Thread
Results 1 to 5 of 5

How to sort number with hypens and those without separately

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Queensland,Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    How to sort number with hypens and those without separately

    GOS-12-3704
    GOS-12-3712
    GOS-12-3782
    GOS1240
    GOS-12-4052
    GOS1241
    GOS1242


    Hi everyone

    I am new to the forum and hope you can help me.

    Can usually find what I need, but stumped this time.

    I would like to sort this list so that the hypenated and non hypenated numbers sort themselves into two groups.

    Have tried as text and tried as number but see the same result.

    I need to do this so that I can fix the incorrect numbers, all the numbers should have text-two numbers- text-up to 5 numbers

    Thank you

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to sort number with hypens and those without separately

    Assume that the data is present from A1:A7 cell.

    In B1 cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

    Apply Sort for Column-B.
    Last edited by :) Sixthsense :); 04-24-2013 at 04:09 AM. Reason: Simplified the formula


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    05-22-2012
    Location
    Queensland,Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to sort number with hypens and those without separately

    Thank you for your help, but this still does not resolve the problem.

    Should have explained it more fully.

    The first three letters will change depending on what I am working on, the next two numbers will also change, so cannot just look for specifics.

    In the list below would like to sort so that the hypenated numbers and non hypenated numbers group together and are not mixed together.

    Thank you.

    GOS1227
    GOS-12-2712
    GOS1233
    GOS-12-3311
    GOS1234
    GOS-12-3404
    GOS-12-3429
    GOS-12-3442
    GOS-12-3450
    GOS-12-3487
    GOS-12-3492

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to sort number with hypens and those without separately

    Hi and welcome to the forum

    Try this. With your data in column A, add a helper column to B (or any column) and copy this down...
    =IF(ISERROR(SEARCH("-",A1,1)),0,1)

    Then sort based on B and then A
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-22-2012
    Location
    Queensland,Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to sort number with hypens and those without separately

    Thank you for your welcome and thank you for the formula FDibbins, just perfect, can continue on now.

+ 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