MS Office Forum / Excel / New Users / February 2008
alphabetize within a cell
|
|
Thread rating:  |
hiflyinskiier@gmail.com - 14 Feb 2008 20:31 GMT if i have a list of letters in a cell, is it possible to alphabetize them?
example: ADCB --> ABCD
Ron Rosenfeld - 14 Feb 2008 20:41 GMT >if i have a list of letters in a cell, is it possible to alphabetize >them? > >example: ADCB --> ABCD If your cells are contiguous and in a single column, here is a method that will sort each cell and write the result into the cell in the adjacent column. It can be easily modified for different situations.
To enter this Sub, <alt><F11> opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens.
To use this, select some cell in the column of cells to be sorted. Then <alt><F8> opens the macro dialog box. Select the macro and run.
Once satisfied that it is doing what you want, you can remove the "offset" function from the line that writes the result, and overwrite the original, if you want.
======================================= Sub SortCell() Dim Source As Range, Target As Range Dim c As Range Dim i As Long Dim Temp()
Set Target = Selection.CurrentRegion.Offset(0, 1) Set Target = Target.Resize(, 1) Target.Clear Set Source = Selection.CurrentRegion
For Each c In Source ReDim Temp(0 To Len(c.Text) - 1) For i = 0 To UBound(Temp) Temp(i) = Mid(c.Text, i + 1, 1) Next i SingleBubbleSort Temp c.Offset(0, 1).Value = Join(Temp, "") Next c End Sub
Function SingleBubbleSort(TempArray As Variant) 'copied directly from support.microsoft.com Dim Temp As Variant Dim i As Integer Dim NoExchanges As Integer
' Loop until no more "exchanges" are made. Do NoExchanges = True
' Loop through each element in the array. For i = 0 To UBound(TempArray) - 1
' If the element is greater than the element ' following it, exchange the two elements. If TempArray(i) > TempArray(i + 1) Then NoExchanges = False Temp = TempArray(i) TempArray(i) = TempArray(i + 1) TempArray(i + 1) = Temp End If Next i Loop While Not (NoExchanges)
End Function ================================ --ron
hiflyinskiier@gmail.com - 14 Feb 2008 21:04 GMT > >if i have a list of letters in a cell, is it possible to alphabetize > >them? [quoted text clipped - 65 lines] > ================================ > --ron two things on that 1) is there a way to get it to go into the same cell (instead of the one next to it) 2) is there a way to get it to alphabetize uppercase/lowercase letters as one. (example: ABaC --> AaBC), where the current program does ABaC --> ABCa
hiflyinskiier@gmail.com - 14 Feb 2008 21:08 GMT On Feb 14, 1:04 pm, hiflyinski...@gmail.com wrote:
> > >if i have a list of letters in a cell, is it possible to alphabetize > > >them? [quoted text clipped - 72 lines] > as one. (example: ABaC --> AaBC), where the current program does ABaC > --> ABCa also, to clarify: if it is not possible to get it to go into the same cell, if one formula could process an entire worksheet and put the results into a new worksheet
Ron Rosenfeld - 14 Feb 2008 22:52 GMT >also, to clarify: >if it is not possible to get it to go into the same cell, if one >formula could process an entire worksheet and put the results into a >new worksheet If you read ALL of my response, you will see the answer to this question of yours. --ron
Ron Rosenfeld - 14 Feb 2008 22:55 GMT >2) is there a way to get it to alphabetize uppercase/lowercase letters >as one. (example: ABaC --> AaBC), where the current program does ABaC >--> ABCa To make the routine case insensitive, change this line:
If TempArray(i) > TempArray(i + 1) Then
to this:
If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then --ron
kounoike - 15 Feb 2008 02:07 GMT >>2) is there a way to get it to alphabetize uppercase/lowercase letters >>as one. (example: ABaC --> AaBC), where the current program does ABaC [quoted text clipped - 8 lines] > If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then > --ron I might be wrong, but I don't think only this change will satisfy the requirement.
keiji
Ron Rosenfeld - 15 Feb 2008 02:15 GMT >>>2) is there a way to get it to alphabetize uppercase/lowercase letters >>>as one. (example: ABaC --> AaBC), where the current program does ABaC [quoted text clipped - 11 lines] >I might be wrong, but I don't think only this change will satisfy the >requirement. Since I am unable to read your mind, perhaps you could share your reasoning for making that comment. --ron
kounoike - 15 Feb 2008 04:03 GMT > On Fri, 15 Feb 2008 11:07:52 +0900, "kounoike" > <kounoike@nowherembh.nifty.com> > wrote: --snip
>>> To make the routine case insensitive, change this line: >>> [quoted text clipped - 12 lines] > making that comment. > --ron sorry for unclear comment. if upper case necessarily come before lower case is the requirement, i wonder this change could gurantee it.
Ron Rosenfeld - 15 Feb 2008 12:21 GMT >sorry for unclear comment. if upper case necessarily come before lower case >is the requirement, i wonder this change could gurantee it. No, it would not. But it was not designed to do that. Rather, it was designed to "alphabetize uppercase/lowercase letters as one".
To me, that statement of the requirement means to treat them as being identical, hence, no ordering between different cases of the same letter.
If the OP wants to have a custom sort order, rather than merely a case-insensitive sort order, that can be done without too much difficulty. But then I'd like to confirm, for example, that *only* letters [A-Za-z] are in the cell, as he stated in his first post. Or, if not, how he would want any additional characters sorted. --ron
kounoike - 15 Feb 2008 12:50 GMT > On Fri, 15 Feb 2008 13:03:37 +0900, "kounoike" > <kounoike@nowherembh.nifty.com> [quoted text clipped - 19 lines] > additional characters sorted. > --ron Thank you for your comment and i see your point. the requrirement might be my one-sided understanding, but i would like to leave it to the OP.
keiji
Ron Rosenfeld - 15 Feb 2008 13:27 GMT >Thank you for your comment and i see your point. the requrirement might be >my one-sided understanding, but i would like to leave it to the OP. > >keiji I would also. But what he posted did not seem to me to specify anything other than treating case-differentiated letters as the same. --ron
Ron Rosenfeld - 15 Feb 2008 12:41 GMT >> On Fri, 15 Feb 2008 11:07:52 +0900, "kounoike" >> <kounoike@nowherembh.nifty.com> [quoted text clipped - 21 lines] >sorry for unclear comment. if upper case necessarily come before lower case >is the requirement, i wonder this change could gurantee it. Actually, to do the case insensitive sort, one only needs to add
Option Compare Text to the top of the module. --ron
Ron Rosenfeld - 15 Feb 2008 12:42 GMT >>2) is there a way to get it to alphabetize uppercase/lowercase letters >>as one. (example: ABaC --> AaBC), where the current program does ABaC [quoted text clipped - 8 lines] >If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then >--ron I should alter that recommendation.
To do a case insensitive sort, do NOT make the change I stated above.
Rather, at the very top of the module enter
Option Compare Text
So the beginning should now read:
===================== Option Explicit Option Compare Text Sub SortCell() ... ==================== --ron
|
|
|