Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro 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.
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 article
- 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.
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.
- Click on macro SortValuesInCell to select it.
- Click "Run" button to execute the macro.
- The macro shows a input box and prompts for a cell range, select a cell range.
- Click 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.
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
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
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.
- Click on "Insert" on the menu.
- Click 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. Remember, you can't undo the changes the macro does to your spreadsheet. Create a backup first.
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Extract a unique distinct list sorted from A to Z ignore blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Sort dates within a date range
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 […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Lookup and return multiple sorted values based on corresponding values in another column
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
5 Responses to “Sort values in a cell based on a delimiting character [VBA]”
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 downloaded 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