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

TJP

New Member
Joined
Sep 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

TJP

New Member
Joined
Sep 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,533
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

Cell Formulas
RangeFormula
B1B1=TEXTJOIN(CHAR(10),,SORT(TEXTSPLIT(A1,CHAR(10)),,,1))
C1C1=TEXTJOIN(CHAR(10),,SORT(FILTERXML("<p><c>"&SUBSTITUTE(A1,CHAR(10),"</c><c>")&"</c></p>","//c")))
 
Upvote 0
Solution

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,329
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.

VBA Code:
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
 
Upvote 0

TJP

New Member
Joined
Sep 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

Cell Formulas
RangeFormula
B1B1=TEXTJOIN(CHAR(10),,SORT(TEXTSPLIT(A1,CHAR(10)),,,1))
C1C1=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!
 
Upvote 0

TJP

New Member
Joined
Sep 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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!
 
Upvote 0

TJP

New Member
Joined
Sep 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
The solution suggested by Peter and Mike, both works perfect.
I am unable to mark both of them as a solution.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,533
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,186,654
Messages
5,959,013
Members
438,392
Latest member
lopingmonkey182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top