# 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 […]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]

Today I would like to share with you these small event handler procedures that make it easier for you to […]

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

### Sort values category

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

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 […]

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## 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.

**Contact Oscar**

You can contact me through this contact form

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