Solved: Splitting cell content

[SeanJ]

Ok everyone I have a question for you guys. In column ?A? I have in each cell letters and numbers. What I want to do is to place the letters and number in a certain order in column ?C?. The order in Column ?C? is this number will be first in numerical order then letter in alphabetical order. Please see example below.


___A___ ___C____

A2 2A
M4A1 14AM
B93O 39BO



In column ?A? will be no more then 20 rows in which I will input the values. I need a starting point for either coding in VBA or a formula in a cell in column "C".

[mdmackillop]

Here's a UDF solution. Paste the code in a standard module. Enter =SORTIT(A1) in a cell and copy down. If you prefer the code another way, let us know.

[vba]
Option Explicit
Function SortIt(Data As Range)
Dim i As Long
Dim arr()
Dim str As String
ReDim arr(Len(Data) - 1)
'Add the contents to an array
For i = 1 To Len(Data)
arr(i - 1) = Mid(Data, i, 1)
Next
'Sort the array
Quick_Sort arr, 0, UBound(arr)
'Create a string from the array
For i = 1 To Len(Data)
str = str & arr(i - 1)
Next
SortIt = str
End Function


Private Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator As Variant
Low = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
Do While (SortArray(Low) < List_Separator)
Low = Low + 1
Loop
Do While (SortArray(High) > List_Separator)
High = High - 1
Loop
If (Low <= High) Then
Temp = SortArray(Low)
SortArray(Low) = SortArray(High)
SortArray(High) = Temp
Low = Low + 1
High = High - 1
End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub

[/vba]

[SeanJ]

That works and thank you.