+ Reply to Thread
Results 1 to 6 of 6

Sort By Only The Numbers In Front Of The Commas

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Alabama
    MS-Off Ver
    Office for Mac 2011
    Posts
    26

    Sort By Only The Numbers In Front Of The Commas

    I have a list of numbers in a column as follows:

    2
    55
    37
    8
    22, 17
    3, 12, 47

    When I sort with my macro I get:

    2
    8
    37
    55
    3, 12, 47
    22, 17

    What I want is:

    2
    3, 12, 47
    8
    22, 17
    37
    55

    Is there a way in vba to have it sort the list using only the number in front of the first comma?
    Thank you.

  2. #2
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Sort By Only The Numbers In Front Of The Commas

    I would copy and paste the data from column 1 to column 2. Use the text to columns function and use the comma as the delimiter. Sort on column 2.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sort By Only The Numbers In Front Of The Commas

    Try this formula in B2

    =IFERROR(--LEFT(A2,FIND(",",A2)-1),A2)

    then filter by column B

    A
    B
    1
    Range Filter
    2
    2
    2
    3
    3, 12, 47
    3
    4
    8
    8
    5
    22, 17
    22
    6
    37
    37
    7
    55
    55
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    09-14-2012
    Location
    Alabama
    MS-Off Ver
    Office for Mac 2011
    Posts
    26

    Re: Sort By Only The Numbers In Front Of The Commas

    Quote Originally Posted by AlKey View Post
    Try this formula in B2

    =IFERROR(--LEFT(A2,FIND(",",A2)-1),A2)

    then filter by column B

    A
    B
    1
    Range Filter
    2
    2
    2
    3
    3, 12, 47
    3
    4
    8
    8
    5
    22, 17
    22
    6
    37
    37
    7
    55
    55
    Thanks, that works. I had to have vba put the formula into the cell because other code comes first and sorts and deletes rows in the table first. But it works great. Thanks again.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sort By Only The Numbers In Front Of The Commas

    You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  6. #6
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Sort By Only The Numbers In Front Of The Commas

    If you wanted to do the whole thing (including the sort) in VBA without using a helper column this works:

    Please Login or Register  to view this content.
    Regards,

    Tom

+ 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. Replies: 5
    Last Post: 01-17-2014, 05:56 PM
  2. Need help with putting an 'S' in front of numbers
    By travhaughton7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2013, 10:29 AM
  3. [SOLVED] 2 separate columns with numbers separated by commas and single numbers not, need sum
    By Slothbob in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2013, 04:33 PM
  4. Adding numbers with USD in front
    By IamRustyP in forum Excel General
    Replies: 7
    Last Post: 04-11-2011, 03:53 PM
  5. placing numbers in front of existing numbers
    By Nickaru in forum Excel General
    Replies: 2
    Last Post: 08-17-2010, 02:29 PM

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