Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
Hello all,
I am attempting to sort text within a cell. The text will mainly be numbers, but the numbers may have some additional alpha characters in front of it which do not affect the sorting order as only the numeric values do. For instance, if I have the following 67432, 34321, 43456, imp41431, 644, imj1123 The sorted items should look like the following 644, imj1123, 34321, imp41431, 43456, 67432 I did find a thread entitled "alphabetizing/sorting text within a cell", but did doesn't exactly do what I need. Also, I really don't want a function per se as I need to the text in the cell to be reorder, but not reorder in another cell. Perhaps some sort of macro might do the trick? At this point, it is beyond my current knowledge of Excel so any help would be greatly appreciated. Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
Hi Mark,
You could use 'Text to Columns' delimited option to put your data into separate cells then sort the cells the way you want and then recombine the data to a single cell by concatenating. e.g. original data in A1 Use text to columns Data is now in cells A1,B1,C1,D1,E1,F1 Now sort your data how you wish. Then in G1 put this formula =A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1 Now select columns A1 to F1 (entire columns) and delete them Your data will now be sorted and back in place in cell A1. HTH Martin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
Oops!
Sorry about this, But I didn't notice the disappearance of one of the substrings due to a bit of confusion with the split method returning a zero base array, while the other arrays were one base. this version retains all substrings... Public Sub SortCellStrings() Dim rngSortRange As Range Set rngSortRange = Application.InputBox( _ "Select the range of cells for internal sorting", _ "Sort Cell Contents", Selection.Address, , , , , 8) Dim rngCell As Range Dim StrSubStrings() As String Dim strNumPart As String Dim vaArray() As Variant Dim lNum As Long Dim I As Integer Dim J As Integer For Each rngCell In rngSortRange StrSubStrings = Split(rngCell.Value, ", ") For I = 0 To UBound(StrSubStrings) For J = 1 To Len(StrSubStrings(I)) Select Case Mid(StrSubStrings(I), J, 1) Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" strNumPart = strNumPart & Mid(StrSubStrings(I), J, 1) End Select Next J ReDim Preserve vaArray(2, I + 1) As Variant vaArray(1, I + 1) = StrSubStrings(I) vaArray(2, I + 1) = CLng(strNumPart) strNumPart = "" Next I BubbleSort vaArray:=vaArray ReDim strFinal(0 To UBound(vaArray, 2)) As String For I = 0 To UBound(vaArray, 2) - 1 strFinal(I) = vaArray(1, I + 1) Next I rngCell.Value = Join(strFinal, ", ") Next rngCell End Sub Public Sub BubbleSort(vaArray() As Variant) Dim J As Integer, k As Integer, l As Integer, n As Integer, t$, u$ n = UBound(vaArray, 2) For l = 0 To n J = l For k = J + 1 To n If vaArray(2, k) <= vaArray(2, J) Then J = k End If Next k If l < J Then t$ = vaArray(2, J) u$ = vaArray(1, J) vaArray(2, J) = vaArray(2, l) vaArray(1, J) = vaArray(1, l) vaArray(2, l) = t$ vaArray(1, l) = u$ End If Next l End Sub Ken Johnson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
Ken,
Sorry for the long response, but thank you very much for the code as it works very nice. Thanks a lot!. Mark Ken Johnson wrote: Oops! Sorry about this, But I didn't notice the disappearance of one of the substrings due to a bit of confusion with the split method returning a zero base array, while the other arrays were one base. this version retains all substrings... Public Sub SortCellStrings() Dim rngSortRange As Range Set rngSortRange = Application.InputBox( _ "Select the range of cells for internal sorting", _ "Sort Cell Contents", Selection.Address, , , , , 8) Dim rngCell As Range Dim StrSubStrings() As String Dim strNumPart As String Dim vaArray() As Variant Dim lNum As Long Dim I As Integer Dim J As Integer For Each rngCell In rngSortRange StrSubStrings = Split(rngCell.Value, ", ") For I = 0 To UBound(StrSubStrings) For J = 1 To Len(StrSubStrings(I)) Select Case Mid(StrSubStrings(I), J, 1) Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" strNumPart = strNumPart & Mid(StrSubStrings(I), J, 1) End Select Next J ReDim Preserve vaArray(2, I + 1) As Variant vaArray(1, I + 1) = StrSubStrings(I) vaArray(2, I + 1) = CLng(strNumPart) strNumPart = "" Next I BubbleSort vaArray:=vaArray ReDim strFinal(0 To UBound(vaArray, 2)) As String For I = 0 To UBound(vaArray, 2) - 1 strFinal(I) = vaArray(1, I + 1) Next I rngCell.Value = Join(strFinal, ", ") Next rngCell End Sub Public Sub BubbleSort(vaArray() As Variant) Dim J As Integer, k As Integer, l As Integer, n As Integer, t$, u$ n = UBound(vaArray, 2) For l = 0 To n J = l For k = J + 1 To n If vaArray(2, k) <= vaArray(2, J) Then J = k End If Next k If l < J Then t$ = vaArray(2, J) u$ = vaArray(1, J) vaArray(2, J) = vaArray(2, l) vaArray(1, J) = vaArray(1, l) vaArray(2, l) = t$ vaArray(1, l) = u$ End If Next l End Sub Ken Johnson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort text that is deliminated within a cell
Hi Mark,
You're welcome. Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text not continuing to wrap for large block of text in Excel cell | Charts and Charting in Excel | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Cell Capacity - text | Excel Discussion (Misc queries) | |||
Can you keep text from one cell showing over the next cell? | Excel Discussion (Misc queries) | |||
Shade cell according to text? | Excel Discussion (Misc queries) |