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?
"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.
Can be done with a user defined function.
Alt F11 to open the VBA editor. Insert - module. Paste in the above. Alt F11 back to worksheet,
Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "") MyArray = Split(CelltoSortString, DelimitingCharacter) For N = 0 To UBound(MyArray) For M = 1 To UBound(MyArray) If MyArray(M) < MyArray(M - 1) Then TempValue = MyArray(M) MyArray(M) = MyArray(M - 1) MyArray(M - 1) = TempValue End If Next M Next N For N = 0 To UBound(MyArray) SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter Next N SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1) If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ") End Function
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.
Can be done with a user defined function.
Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "") MyArray = Split(CelltoSortString, DelimitingCharacter) For N = 0 To UBound(MyArray) For M = 1 To UBound(MyArray) If MyArray(M) < MyArray(M - 1) Then TempValue = MyArray(M) MyArray(M) = MyArray(M - 1) MyArray(M - 1) = TempValue End If Next M Next N For N = 0 To UBound(MyArray) SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter Next N SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1) If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ") End Function
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
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?
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.Originally Posted by mrice
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
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.
Ignore, ignore....I've got it figured out...you are AWESOME!!!!!!!!!!!
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.
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.
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?
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.
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
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]
So how do I get it to do it in reverse order
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
Hi ,
I would like to sort one word per cell.
For example
JCDELBVGAQS
result:
ABCDEHJLQSV
Thannks,
Archana
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks