Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell range from A to Z. It sorts the values in a cell each not cells combined.
What's on this page
1 Sort values in a single cell based on a delimiting character (Formula)
The image above shows a formula in cell D3 that sorts multiple values in cell B3 based on a delimiting character. The formula returns an array of values, Excel 365 users may enter the formula as a regular formula, however, previous versions must enter the formula as an array formula.
The FILTERXML function is an Excel 2013 function.
Formula in cell D3:
Update! The new TEXTSPLIT function available for Excel 365 users.
Excel 365 dynamic array formula in cell D3:
1.1 Explaining formula in cell D3
Step 1 - Replace the delimiting character with a XML tag name
The SUBSTITUTE function substitutes a given string with another string, all found instances are replaced.
SUBSTITUTE(B3, ",", "</a><a>")
becomes
SUBSTITUTE("c,b,m, v, b , a",",","</a><a>")
and returns
"c</a><a>b</a><a>m</a><a> v</a><a> b </a><a> a"
Step 2 - Concatenate XML tags
The ampersand character concatenates strings in an excel formula.
"<b><a>"&SUBSTITUTE(B3, ",", "</a><a>")&"</a></b>"
becomes
"<b><a>"&"c</a><a>b</a><a>m</a><a> v</a><a> b </a><a> a"&"</a></b>"
and returns
"<b><a>c</a><a>b</a><a>m</a><a> v</a><a> b </a><a> a</a></b>"
Step 3 - Extract XML data
FILTERXML("<b><a>"&SUBSTITUTE(B3, ",", "</a><a>")&"</a></b>","//a")
becomes
FILTERXML("<b><a>c</a><a>b</a><a>m</a><a> v</a><a> b </a><a> a</a></b>","//a")
and returns {"c";"b";"m";"v";"b";"a"}.
Note that leading and trailing blanks are automatically removed.
Step 4 - Sort array values
SORT(FILTERXML("<b><a>"&SUBSTITUTE(B3, ",", "</a><a>")&"</a></b>","//a"))
becomes
SORT({"c";"b";"m";"v";"b";"a"})
and returns {"a";"b";"b";"c";"m";"v"}.
2 Sort values in a single cell based on a delimiting character (Macro)
The macro asks you for a delimiting character and based on that character it creates an array of values and returns those values concatenated and sorted.
What you will learn in this section
- How to use an inputbox programmatically.
- How to iterate through cells using VBA.
- How to split values in a cell programmatically.
- How to send an array to another macro/User defined function using VBA.
- How to sort arrays from A to Z.
- How to concatenate values in an array using VBA.
2.1 How this macro works
The animated image above shows you how to start the macro and use the macro.
- Make sure you have made a backup of your workbook before running this macro.
- Press Alt + F8 to open the macro dialog box.
- Press with mouse on macro SortValuesInCell to select it.
- Press with left mouse button on "Run" button to run the macro.
- The macro shows a input box and prompts for a cell range, select a cell range.
- Press with left mouse button on OK button.
- The macro asks for a delimiting character, type it and then press OK button.
- The macro returns the values sorted in the same cells as they were fetched from.
2.2 VBA Code
'Name macro Sub SortValuesInCell() 'Dimension variables and declare data types Dim rng As Range Dim cell As Range Dim del As String Dim Arr As Variant 'Enable error handling On Error Resume Next 'Show an inputbox and ask for a cell range Set rng = Application.InputBox(Prompt:="Select a cell range:", _ Title:="Sort values in a single cell", _ Default:=Selection.Address, Type:=8) 'Show an inputbox and ask for a delimiting character del = InputBox(Prompt:="Delimiting character:", _ Title:="Sort values in a single cell", _ Default:="") 'Disable error handling On Error GoTo 0 'Iterate through each cell in cell range For Each cell In rng 'Split values based on the delimiting character and save those to an array variable Arr = Split(cell, del) 'Sort array using a second user defined function SelectionSort Arr 'Concatenate array using the same delimiting character cell = Join(Arr, del) 'Continue with next cell Next cell End Sub
2.3 Sort algorithm
The following user defined function sorts the contents in an array.
'Name user defined function and dimension arguments and declare data types Function SelectionSort(TempArray As Variant) 'Dimension variables and declare data types Dim MaxVal As Variant Dim MaxIndex As Integer Dim i As Integer, j As Integer ' Step through the elements in the array starting with the ' last element in the array. For i = UBound(TempArray) To 0 Step -1 ' Set MaxVal to the element in the array and save the ' index of this element as MaxIndex. MaxVal = TempArray(i) MaxIndex = i ' Loop through the remaining elements to see if any is ' larger than MaxVal. If it is then set this element ' to be the new MaxVal. For j = 0 To i If TempArray(j) > MaxVal Then MaxVal = TempArray(j) MaxIndex = j End If Next j ' If the index of the largest element is not i, then ' exchange this element with element i. If MaxIndex < i Then TempArray(MaxIndex) = TempArray(i) TempArray(i) = MaxVal End If Next i End Function
2.4 Where to put the code?
- Copy above VBA code.
- Press Alt+ F11 to open the Visual Basic Editor.
- Select your workbook in the Project Explorer.
- Press with mouse on "Insert" on the menu.
- Press with mouse on "Module" to create a module.
- Paste VBA code to code module.
- Return to Excel.
I used the "Sort array" function found here:
Using a Visual Basic Macro to Sort Arrays in Microsoft Excel (Microsoft)
Edit: That link is now broken and I don't know where the code is located now, I have added the SelectionSort function code to this article with some small modifications.
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
Excel categories
8 Responses to “Sort values in a cell based on a delimiting character”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
this code doesn't sort the natural numbers
1. this way: 0, 1, 2, 3, 11, 15, 22
2. code is sorting this way : 0, 1, 11, 15, 2, 22, 3
will it be possible to sort in no.1 type...?
Thanks!
Really helpful!
[…] How to sort values in one cell using a custom delimiter [Get Digital Help] […]
[…] How to sort values in one cell using a custom delimiter [Get Digital Help] […]
I opened your cell.xlsm spreadsheet example. It worked for the most part except it placed the first entry in the cell list at the end. I assume because it was the only value not preceded by a comma.
How do I fix that? Thanks, Jeff
Hi,I’m trying to sort values in particular cell that contains 1/6/unverified/2, I need to change them to 1/6/2/unverified i.e., the work unverified should come to last in a cell. Can you help me how to do this using vba
how to transpose data in MSExcel?
e.g
Name
Age
Sex
will become
Name|Age|Sex,.....
TRANSPOSE function
TEXTJOIN function