alphabetize within a cell

  • Thread starter Thread starter hiflyinskiier
  • Start date Start date
H

hiflyinskiier

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

example: ADCB --> ABCD
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
Back
Top