How can I reorder a CELL alphabetically in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I am trying to reorder letters in each cell and have no idea if there is
an easier way to do it. For example cell 1 might be ASGD and i want to order
it (and millions of others) so it says ADGS.

Any ideas please???
 
Hi - I am trying to reorder letters in each cell and have no idea if there is
an easier way to do it. For example cell 1 might be ASGD and i want to order
it (and millions of others) so it says ADGS.

Any ideas please???
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.

=======================================
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
 
One way is to use a User Defined Function.

Option Explicit
Function SortTheCell(rng As Range) As String

Dim mySplit() As String
Dim Temp As String
Dim iCtr As Long
Dim jCtr As Long
Dim myStr As String

Set rng = rng(1)

If rng.Value = "" Then
SortTheCell = ""
Exit Function
End If

ReDim mySplit(1 To Len(rng.Value))
For iCtr = 1 To Len(rng.Value)
mySplit(iCtr) = Mid(rng.Value, iCtr, 1)
Next iCtr

For iCtr = LBound(mySplit) To UBound(mySplit) - 1
For jCtr = iCtr + 1 To UBound(mySplit)
If LCase(mySplit(iCtr)) > LCase(mySplit(jCtr)) Then
Temp = mySplit(iCtr)
mySplit(iCtr) = mySplit(jCtr)
mySplit(jCtr) = Temp
End If
Next jCtr
Next iCtr

myStr = ""
For iCtr = LBound(mySplit) To UBound(mySplit)
myStr = myStr & mySplit(iCtr)
Next iCtr
SortTheCell = myStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
type some text into A1
and type this into B1:
=sortthecell(a1)
 
Back
Top