alphabetize within a cell

H

hiflyinskiier

if i have a list of letters in a cell, is it possible to alphabetize
them?

example: ADCB --> ABCD
 
R

Ron Rosenfeld

if i have a list of letters in a cell, is it possible to alphabetize
them?

example: ADCB --> ABCD
Click to expand...

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
 
H

hiflyinskiier

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
Click to expand...

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
 
H

hiflyinskiier

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

If you read ALL of my response, you will see the answer to this question of
yours.
--ron
 
R

Ron Rosenfeld

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
Click to expand...


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
 
K

kounoike

Ron Rosenfeld said:
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
Click to expand...

I might be wrong, but I don't think only this change will satisfy the
requirement.

keiji
 
R

Ron Rosenfeld

I might be wrong, but I don't think only this change will satisfy the
requirement.
Click to expand...

Since I am unable to read your mind, perhaps you could share your reasoning for
making that comment.
--ron
 
K

kounoike

Ron Rosenfeld said:
On Fri, 15 Feb 2008 11:07:52 +0900, "kounoike"
<[email protected]>
wrote:
--snip


Since I am unable to read your mind, perhaps you could share your
reasoning for
making that comment.
--ron
Click to expand...

sorry for unclear comment. if upper case necessarily come before lower case
is the requirement, i wonder this change could gurantee it.
 
R

Ron Rosenfeld

sorry for unclear comment. if upper case necessarily come before lower case
is the requirement, i wonder this change could gurantee it.
Click to expand...

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
 
R

Ron Rosenfeld

sorry for unclear comment. if upper case necessarily come before lower case
is the requirement, i wonder this change could gurantee it.
Click to expand...

Actually, to do the case insensitive sort, one only needs to add

Option Compare Text to the top of the module.
--ron
 
R

Ron Rosenfeld

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
Click to expand...


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
 
K

kounoike

Ron Rosenfeld said:
sorry for unclear comment. if upper case necessarily come before lower
case
is the requirement, i wonder this change could gurantee it.
Click to expand...

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top