Sorting the data in a single cell in ascending/ descending order

[TJP]

There is a list of alpha numeric data in each cell of the excel. I am not able to sort these numbers within the cell in any way, other than manually copying the list from each cell into notepad, remove the " " from the list and then copy and paste in on a different excel sheet, sort in ascending, Copy+Paste on notepad and then Copy + paste to the original cell from where the data is taken.
Can anyone please help me automate this task with a VBA code, this will make my life easier.
I tried to extract with little knowledge on coding, however, looks like the data in each cell is being considered as a single word.
ex: below is the data in one of the cells which I want to sort (Like wise there are 500+ rows with different list of data in each row). I need to sort by (LP)s and (P)s

(LP)ABC-GH-857
(LP)ABC-GH-754
(P)ABC-GH-755
(P)ABC-GH-756
(LP)ABC-GH-776
(P)ABC-GH-858
(LP)ABC-GH-746
(P)ABC-GH-768
(P)ABC-GH-846
(P)ABC-GH-749
(LP)ABC-GH-854

 

[nageswararao]

try this formula in cell B1

=SORT(a1:a11,,1)

 

[TJP]

Thanks for reverting.
However, the entire data is inside a single cell. Ex: the below list is in A1 alone not distributed through A1 to A11.
(LP)ABC-GH-857
(LP)ABC-GH-754
(P)ABC-GH-755
(P)ABC-GH-756
(LP)ABC-GH-776
(P)ABC-GH-858
(LP)ABC-GH-746
(P)ABC-GH-768
(P)ABC-GH-846
(P)ABC-GH-749
(LP)ABC-GH-854

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of options, depending on whether or not you have the TEXTSPLIT function yet.

TJP.xlsm
	A	B	C
1	(LP)ABC-GH-857 (LP)ABC-GH-754 (P)ABC-GH-755 (P)ABC-GH-756 (LP)ABC-GH-776 (P)ABC-GH-858 (LP)ABC-GH-746 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-749 (LP)ABC-GH-854	(LP)ABC-GH-746 (LP)ABC-GH-754 (LP)ABC-GH-776 (LP)ABC-GH-854 (LP)ABC-GH-857 (P)ABC-GH-749 (P)ABC-GH-755 (P)ABC-GH-756 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-858	(LP)ABC-GH-746 (LP)ABC-GH-754 (LP)ABC-GH-776 (LP)ABC-GH-854 (LP)ABC-GH-857 (P)ABC-GH-749 (P)ABC-GH-755 (P)ABC-GH-756 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-858
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(CHAR(10),,SORT(TEXTSPLIT(A1,CHAR(10)),,,1))
C1	C1	=TEXTJOIN(CHAR(10),,SORT(FILTERXML("<p><c>"&SUBSTITUTE(A1,CHAR(10),"</c><c>")&"</c></p>","//c")))

 

[mikerickson]

Data in a single cell is considered a single data point, which is why splitting the data one item per cell is the best thing to do with your data for downstream processing.
That being said, this UDF will sort a delimited string. The optional Delimiter argument defaults to space.

Function SortDelimitedString(aString As String, Optional Delimiter As String = " ", Optional Descending As Boolean = False, Optional CaseSensitive As Boolean = False)
    Dim Words As Variant
    Dim strLeft As String, strPivot As String, strRight As String
    Dim i As Long, flag As Boolean
    
    If aString = vbNullString Then
        strPivot = vbNullString
    Else
        Words = Split(aString, Delimiter)
        
        strPivot = Words(0)
        
        For i = 1 To UBound(Words)
           If CaseSensitive Then
                flag = Words(i) < strPivot
           Else
                flag = LCase(Words(i)) < LCase(strPivot)
           End If
           flag = flag Xor Descending
           
           If flag Then
                strLeft = strLeft & Delimiter & Words(i)
            Else
                strRight = strRight & Delimiter & Words(i)
            End If
        Next i
        
        strLeft = Mid(strLeft, Len(Delimiter) + 1)
        strRight = Mid(strRight, Len(Delimiter) + 1)
    End If
    
    SortDelimitedString = strPivot
    If strRight <> vbNullString Then
        SortDelimitedString = SortDelimitedString & Delimiter & SortDelimitedString(strRight, Delimiter, Descending, CaseSensitive)
    End If
    If strLeft <> vbNullString Then
        SortDelimitedString = SortDelimitedString(strLeft, Delimiter, Descending, CaseSensitive) & Delimiter & SortDelimitedString
    End If
End Function

 

[TJP]

Welcome to the MrExcel board!

A couple of options, depending on whether or not you have the TEXTSPLIT function yet.

TJP.xlsm
	A	B	C
1	(LP)ABC-GH-857 (LP)ABC-GH-754 (P)ABC-GH-755 (P)ABC-GH-756 (LP)ABC-GH-776 (P)ABC-GH-858 (LP)ABC-GH-746 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-749 (LP)ABC-GH-854	(LP)ABC-GH-746 (LP)ABC-GH-754 (LP)ABC-GH-776 (LP)ABC-GH-854 (LP)ABC-GH-857 (P)ABC-GH-749 (P)ABC-GH-755 (P)ABC-GH-756 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-858	(LP)ABC-GH-746 (LP)ABC-GH-754 (LP)ABC-GH-776 (LP)ABC-GH-854 (LP)ABC-GH-857 (P)ABC-GH-749 (P)ABC-GH-755 (P)ABC-GH-756 (P)ABC-GH-768 (P)ABC-GH-846 (P)ABC-GH-858
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(CHAR(10),,SORT(TEXTSPLIT(A1,CHAR(10)),,,1))
C1	C1	=TEXTJOIN(CHAR(10),,SORT(FILTERXML("<p><c>"&SUBSTITUTE(A1,CHAR(10),"</c><c>")&"</c></p>","//c")))

Thank you very much Peter. There was no TextSplit function hence the second formula (C1) worked. Thanks a ton!

 

[TJP]

Data in a single cell is considered a single data point, which is why splitting the data one item per cell is the best thing to do with your data for downstream processing.
That being said, this UDF will sort a delimited string. The optional Delimiter argument defaults to space.

Function SortDelimitedString(aString As String, Optional Delimiter As String = " ", Optional Descending As Boolean = False, Optional CaseSensitive As Boolean = False)
    Dim Words As Variant
    Dim strLeft As String, strPivot As String, strRight As String
    Dim i As Long, flag As Boolean
   
    If aString = vbNullString Then
        strPivot = vbNullString
    Else
        Words = Split(aString, Delimiter)
       
        strPivot = Words(0)
       
        For i = 1 To UBound(Words)
           If CaseSensitive Then
                flag = Words(i) < strPivot
           Else
                flag = LCase(Words(i)) < LCase(strPivot)
           End If
           flag = flag Xor Descending
          
           If flag Then
                strLeft = strLeft & Delimiter & Words(i)
            Else
                strRight = strRight & Delimiter & Words(i)
            End If
        Next i
       
        strLeft = Mid(strLeft, Len(Delimiter) + 1)
        strRight = Mid(strRight, Len(Delimiter) + 1)
    End If
   
    SortDelimitedString = strPivot
    If strRight <> vbNullString Then
        SortDelimitedString = SortDelimitedString & Delimiter & SortDelimitedString(strRight, Delimiter, Descending, CaseSensitive)
    End If
    If strLeft <> vbNullString Then
        SortDelimitedString = SortDelimitedString(strLeft, Delimiter, Descending, CaseSensitive) & Delimiter & SortDelimitedString
    End If
End Function

Thank you very much Mike. This code helps too!

 

[TJP]

The solution suggested by Peter and Mike, both works perfect.
I am unable to mark both of them as a solution.

 

[mikerickson]

Peter got there first.
I'm glad our solutions worked for you.

 

[Peter_SSs]

Thank you very much Peter.

You're welcome. Thanks for the follow-up.

There was no TextSplit function ..

It will be rolled out to you eventually. Keep an eye out for it and some other useful new TEXT functions.

BTW, for a UDF for data like your sample, another would be

Function SortInCell(s As String) As String
  Dim AL As Object
  Dim itm As Variant
  
  Set AL = CreateObject("System.Collections.ArrayList")
  For Each itm In Split(s, vbLf)
    AL.Add itm
  Next itm
  AL.Sort
  SortInCell = Join(AL.ToArray, vbLf)
End Function