+ Reply to Thread
Results 1 to 3 of 3

Sorting mixed data cells

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2003
    Posts
    15

    Sorting mixed data cells

    Hi all,

    I have a list of dates with an extra number in the back that I would like to sort.


    Data example :
    Please Login or Register  to view this content.
    So basicly excel should first need to sort on the year (last 2 numbers before the "-" ), then the month (middle 2 numbers), then the day (first 2 numbers) and finally the number in the back.
    The only way out I found so far was to make extra columns (or arrays using vba) with the split info and then sort those, thus sorting this. But i'd like to know if there's some way to do it in 1 formula kind of thing.

    Thanks in advance

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sorting mixed data cells

    youll need an extra column minimum i think
    "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

  3. #3
    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
    53,048

    Re: Sorting mixed data cells

    Agree with Martin, use this in a helper, and sort based on the helper...
    =MID(A2,7,2)&"."&MID(A2,4,2)&"."&LEFT(A2,2)&"-"&RIGHT(A2,3)
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sorting Large Data Set with Mixed Data
    By reneeileen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-31-2013, 01:55 PM
  2. Replies: 11
    Last Post: 02-11-2013, 08:10 AM
  3. Replies: 4
    Last Post: 08-25-2012, 07:49 AM
  4. Sorting mixed numeric values
    By cellophane in forum Excel General
    Replies: 6
    Last Post: 05-31-2012, 07:35 AM
  5. Summation of cells with mixed numbers & text
    By HTWingNut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2007, 10:31 AM

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