Closed Thread
Results 1 to 24 of 24

Can you sort data alphabetically in one particular cell?

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Can you sort data alphabetically in one particular cell?

    Column K, Row 4 (or pick a cell) contains the following information:

    Life, Accident & Health, Variable, Legal, Property

    I would like to be able to sort the data in this one cell alphabetically without retyping the data......ie use a sort function but only for a cell.

    In other words, sort the data so that it appears; Accident & Health, Legal, Life, Property, Variable

    Does this function exist?

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Can you sort data alphabetically in one particular cell?

    "Does this function exist?"

    No.

    From excel's perspective, it's just a text string, it doesn't "understand" what it's got, it merely "holds" it, it can't really break it into smaller strings and order them. A function like PROPER() doesn't understand what it's doing, it just follows a rule: make all letters lower-case, except the first letter after a space character, which should be upper case. Excel can't "read".

    You could probably write a macro to do this, though.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Can you sort data alphabetically in one particular cell?

    Can be done with a user defined function.

    Please Login or Register  to view this content.

    Alt F11 to open the VBA editor. Insert - module. Paste in the above. Alt F11 back to worksheet,

    In B1 enter

    =SortWithinCell(A1,",",TRUE)

    assuming starting string is in A1
    Martin

  4. #4
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can you sort data alphabetically in one particular cell?

    Can be done with a user defined function.



    Please Login or Register  to view this content.
    Alt F11 to open the VBA editor. Insert - module. Paste in the above. Alt F11 back to worksheet,

    In B1 enter

    =SortWithinCell(A1,",",TRUE)

    assuming starting string is in A1



    Way above my head, would you mind dumbing it down just a bit? I really appreciate your help.
    Last edited by arlu1201; 11-01-2012 at 07:18 AM.

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Can you sort data alphabetically in one particular cell?

    Does the function work for you or do you want to know how it is working?

  6. #6
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can you sort data alphabetically in one particular cell?

    Quote Originally Posted by mrice View Post
    Does the function work for you or do you want to know how it is working?
    I followed your instructions but its not working. Let me try and help make this a bit easier on both of us.

    The data is in column J cells 1 through 34610. Once I delimit it (after your solution) it will be located in Column J, Column K, Column L, Column M, Column N, Column O, Column P and Column Q

    here is current column J data:

    LINE_OF_AUTHORITY
    Life,Accident & Health
    Life,Accident & Health
    Life,Accident & Health
    Credit Products,Property,Vehicle Physical Damage
    Property,Casualty
    Life,Variable Contracts,Vehicle Physical Damage,Casualty,Property,Accident & Health
    Life,Variable Contracts,Credit Products,Property,Casualty,Vehicle Physical Damage,Accident & Health
    Life,Accident & Health
    Life
    Property,Vehicle Physical Damage,Casualty
    Life,Credit Products
    Life,Property,Accident & Health,Vehicle Physical Damage,Property (Industrial Fire),Casualty
    Casualty,Property
    Life,Property (Industrial Fire),Accident & Health
    Life,Casualty,Vehicle Physical Damage,Variable Contracts,Credit Products,Property,Accident & Health
    Life,Accident & Health
    Life,Variable Contracts,Accident & Health

  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Can you sort data alphabetically in one particular cell?

    Please see the attachment to make things clearer.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can you sort data alphabetically in one particular cell?

    Quote Originally Posted by mrice View Post
    Please see the attachment to make things clearer.
    I'm clearly missing something, very sorry for drilling this down.....


    I've gone ahead and put in the =SortWithinCell (J2,",",TRUE) but it is returning a #NAME? error. FYI - I did not go the alt-11 route, will I still need to do that in order for the formula to work correctly?

    Truly appreciate your help.

  9. #9
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can you sort data alphabetically in one particular cell?

    Ignore, ignore....I've got it figured out...you are AWESOME!!!!!!!!!!!

  10. #10
    Registered User
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Can you sort data alphabetically in one particular cell?

    Damn it!!!!!!!!!! Now that I've got the sort function figured out, when I go and try to delimit the data into individual columns, Excel is now deliminitating the =SortWithinCell(J1,",".TRUE) instead of the text within the box. Now what?

  11. #11
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Can you sort data alphabetically in one particular cell?

    Copy the cells with the formulae and without changing the selection paste special values. This should then work with text to columns.
    Last edited by mrice; 05-25-2012 at 06:52 AM.

  12. #12
    Registered User
    Join Date
    11-01-2012
    Location
    Northants England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Can you sort data alphabetically in one particular cell?

    Hi Martin
    I have cells containing data in a list i.e. delimited with a carriage return not a comma, how do i amend your formula to cater for this and alphabetically arrange the data back into a list within the cell? At the moment I have a list of text values in each cell each consisting of 5 characters e.g. B0690 your solution above gives me an unsorted concatination e.g. B0690B0720B0760B0830B0837B0955B1058B0625 and I need a list (one beneath the other) of alphabetically sorted values. thanks Mike

  13. #13
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Can you sort data alphabetically in one particular cell?

    MJExcel,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  14. #14
    Registered User
    Join Date
    08-05-2015
    Location
    California, USA
    MS-Off Ver
    O4Mac 2011, 14.5.3
    Posts
    1

    Re: Can you sort data alphabetically in one particular cell?

    So how do I get it to do it in reverse order

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Can you sort data alphabetically in one particular cell?

    syem, perhaps you missed reading post #13?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    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

  16. #16
    Registered User
    Join Date
    03-29-2017
    Location
    US
    MS-Off Ver
    10
    Posts
    1

    Re: Can you sort data alphabetically in one particular cell?

    Hi ,

    I would like to sort one word per cell.
    For example

    JCDELBVGAQS

    result:
    ABCDEHJLQSV

    Thannks,
    Archana

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Can you sort data alphabetically in one particular cell?

    Archanapatel welcome to the forum.

    As a new member you may not be familiar with our rules. There aren't many of them. Please take time to read them. One of them is:

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.


    Also see posts #13 and 15 above.

    Looking forward to your new thread.
    Dave

  18. #18
    Registered User
    Join Date
    06-09-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    1

    Re: Can you sort data alphabetically in one particular cell?

    Is it possible to have it ready and available everytime you open a new excel file or will have to paste the code into every new worksheet?

  19. #19
    Registered User
    Join Date
    07-09-2013
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Can you sort data alphabetically in one particular cell?

    How did you fix this? I'm trying to sort but have the #name error as well.

  20. #20
    Registered User
    Join Date
    06-07-2020
    Location
    india
    MS-Off Ver
    2016
    Posts
    2

    Re: Can you sort data alphabetically in one particular cell?

    you need to insert module in vba editor over the sheet you want to make it work with and paste that code shared above and simply close the editor. it doesn't matter the way you opened vba editor.

  21. #21
    Registered User
    Join Date
    06-07-2020
    Location
    india
    MS-Off Ver
    2016
    Posts
    2

    Re: Can you sort data alphabetically in one particular cell?

    Quote Originally Posted by obethgarcia1 View Post
    How did you fix this? I'm trying to sort but have the #name error as well.
    you need to insert module in vba editor over the sheet you want to make it work with and paste that code shared above and simply close the editor. it doesn't matter the way you opened vba editor.

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Can you sort data alphabetically in one particular cell?

    Quote Originally Posted by tiddu View Post
    you need to insert module in vba editor over the sheet you want to make it work with and paste that code shared above and simply close the editor. it doesn't matter the way you opened vba editor.
    I know this is an older thread and that you are new to the forum, but please take a few minutes to read the forum rules - especially regarding posting replies where a moderation is already in place. Thanks

  23. #23
    Registered User
    Join Date
    07-16-2014
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    2

    Re: Can you sort data alphabetically in one particular cell?

    Any updates on this?
    Last edited by jmhatl; 09-30-2021 at 10:47 AM.

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Can you sort data alphabetically in one particular cell?

    Quote Originally Posted by jmhatl View Post
    Any updates on this?
    No, no updates.

    If you have a question, please start your own thread

Closed 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