Alphabetical Sort and Concatenate

Greenthumbherblady

New Member
Joined
Feb 24, 2018
Messages
2
Hello Everyone... is there a way to Alphabetical Sort then concatenate?

For example: If I have TA TT BA BB can excel alphabetical sort all combo then concatenate?

TA TT TA_TT
BA TA TT BA_TA_TT

Thanks much

Theresa
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
would those be individual columns
concatenate, will they all be in the cell when you finish, or have the concatenate in its own column
 
Upvote 0
for the above sample you can use the below VBA function






Excel 2013/2016
AB
1BA TA TTBA_TA_TT
2TA TTTA_TT

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B1=SORT_AND_CONCAT(A1)
B2=SORT_AND_CONCAT(A2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>




code to add:
Code:
Dim objArrL As Object

Function SORT_AND_CONCAT(sInp As String) As String
If objArrL Is Nothing Then Set objArrL = CreateObject("system.collections.arraylist")
Dim s
With objArrL
    .Clear
    For Each s In Split(sInp)
        .Add s
    Next
    .Sort
    SORT_AND_CONCAT = Join(.toarray, "_")
End With
End Function


if you dont know how to add code: https://www.excelguru.ca/content.php?265-Adding-VBA-Code-For-The-First-Time-User
 
Last edited:
Upvote 0
maybe I read this wrong

if that was a to c then in D > =A1&"_"&B1&"_"&C1

and sort on D maybe
 
Upvote 0
Hello Everyone... is there a way to Alphabetical Sort then concatenate?

For example: If I have TA TT BA BB can excel alphabetical sort all combo then concatenate?

TA TT TA_TT
BA TA TT BA_TA_TT
It is hard to tell from your post, but I am assuming the text you want to sort and concatenate are located in individual cells, not all in one cell separated by spaces. Given that, below is a UDF (user defined function) which will do what you want. The function takes two required argument (the range of cells whose values you want to sort and concatenate and the text delimiter you want to put between them) and one optional argument (which allows you to reverse the sort from ascending to descending by passing in TRUE for it). Also note that this function ignores empty cells within the specified range.
Code:
[table="width: 500"]
[tr]
	[td]Function CellSorter(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    CellSorter = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CellSorter just like it was a built-in Excel function. For example,

=CellSorter(A2:C2,"_")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.C
 
Last edited:
Upvote 0
It is hard to tell from your post, but I am assuming the text you want to sort and concatenate are located in individual cells, not all in one cell separated by spaces. Given that, below is a UDF (user defined function) which will do what you want. The function takes two required argument (the range of cells whose values you want to sort and concatenate and the text delimiter you want to put between them) and one optional argument (which allows you to reverse the sort from ascending to descending by passing in TRUE for it). Also note that this function ignores empty cells within the specified range.
Code:
[table="width: 500"]
[tr]
	[td]Function CellSorter(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    CellSorter = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CellSorter just like it was a built-in Excel function. For example,

=CellSorter(A2:C2,"_")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.C
Maybe a better name for the function would be SortConcat....
Code:
[table="width: 500"]
[tr]
	[td]Function SortConcat(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    SortConcat = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

So the example would become...

=SortConcat(A2:C2,"_")
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top