+ Reply to Thread
Results 1 to 5 of 5

How can I change the format of a complex cell so I can sort properly

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    How can I change the format of a complex cell so I can sort properly

    I need some guidance/help. I have a spreadsheet that is exported from an instrument that I would like to sort by Vial. Currently the Vial consists of a letter followed by a number. When I sort it I get A,1, A,10, A,11, A,2, A,3 designation but I would like it to be truly sequential A,1, A,2, A,3....A,10, A,11. I think I need to format the number portion to be 2 digits so that it is A,01, A,02 so that it will align how I need it. Is there a function or formula for me to do this? Do I need a macro??? The number of rows can vary but can exceed 10,000 rows. Please help!!!

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How can I change the format of a complex cell so I can sort properly

    Formula method:
    =LEFT(B2,FIND(",",B2)-1)&","&TEXT(RIGHT(B2,LEN(B2)-FIND(",",B2)),"00")

    VBA method:
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: How can I change the format of a complex cell so I can sort properly

    this code will do the trick

    Please Login or Register  to view this content.
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: How can I change the format of a complex cell so I can sort properly

    Thanks to the both of you, both of them did the trick and your quickness in responding was very much appreciated.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How can I change the format of a complex cell so I can sort properly

    You are welcome and thanks for the rep

+ 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: 2
    Last Post: 03-11-2016, 01:31 AM
  2. Replies: 3
    Last Post: 02-24-2015, 02:58 AM
  3. Replies: 5
    Last Post: 01-28-2015, 10:49 PM
  4. Unable to format a date properly pulled from a cell
    By laxminarayana in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2014, 02:15 PM
  5. [SOLVED] Date Format "d-mmm-yy" as Worksheet Name does not sort properly
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-13-2013, 03:35 PM
  6. [SOLVED] One cell won't sort properly in one column
    By joebell in forum Excel General
    Replies: 2
    Last Post: 11-10-2013, 02:13 AM
  7. Replies: 4
    Last Post: 11-14-2008, 11:21 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