PDA

View Full Version : Solved: Alphabetically reverse text in cell



lhardee
05-03-2008, 07:05 AM
Hello,

Could I bother someone to show me a way to search up a column and reorder text in each cell alphabetically?

Example: Data is in column E

Text in each cell is separated by a space:

ABTRSW AARTIS

The cell example above should be rearranged to:

AARTIS ABTRSW

Thanks,

lhardee

Bob Phillips
05-03-2008, 07:25 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim aryNames As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

aryNames = Split(.Cells(i, "A"), " ")
.Cells(i, "A").Value = Join(BSort(aryNames), " ")
Next i
End With

End Sub

Public Function BSort(InVal As Variant, Optional Order As String = "Asc")
Dim fChanges As Boolean
Dim iElement As Long
Dim iElement2 As Long
Dim temp As Variant
Dim ToSort

ToSort = InVal
Do
fChanges = False
For iElement = LBound(ToSort) To UBound(ToSort) - 1
If ((Order = "Asc" And ToSort(iElement) > ToSort(iElement + 1)) Or _
(Order <> "Asc" And ToSort(iElement) < ToSort(iElement + 1))) Then
'Swap elements
temp = ToSort(iElement)
ToSort(iElement) = ToSort(iElement + 1)
ToSort(iElement + 1) = temp
fChanges = True
End If
Next iElement
Loop Until Not fChanges
BSort = ToSort
End Function

lhardee
05-03-2008, 07:31 AM
WOW xld,

That was a fast response !!! Thank you very much.

Perfect, by the way.

:hi:

lhardee