How to sort a column by a certain keyword/s that is included somewhere in the cells of a column?

How to Sort Excel Data by Keywords Within Cells

Learn effective methods to sort an Excel column based on the presence of specific keywords or text strings within its cells, enhancing data organization and analysis.

Sorting data in Excel is a fundamental skill, but what if you need to sort a column not by its exact cell content, but by whether a cell contains a specific keyword or phrase? This scenario is common when dealing with descriptive text, product names, or log data where relevant information is embedded within longer strings. This article will guide you through several techniques to achieve this specialized sorting, from using helper columns with formulas to more advanced VBA solutions.

Method 1: Using a Helper Column with FIND/SEARCH and IF

The most straightforward approach involves creating a temporary 'helper' column. This column will contain a value that indicates whether your target keyword is present in the corresponding cell of the column you wish to sort. You can then sort your data based on this helper column.

1. Add a Helper Column

Insert a new column next to your data. Let's assume your data is in column A, starting from A2, and you want to sort by the keyword "Apple".

2. Enter the Formula

In the first cell of your helper column (e.g., B2), enter one of the following formulas:

• Case-sensitive search: =IF(ISNUMBER(FIND("Apple",A2)),1,0)
• Case-insensitive search: =IF(ISNUMBER(SEARCH("Apple",A2)),1,0)

These formulas will return 1 if "Apple" is found in cell A2, and 0 otherwise. You can also use text values like "Contains Apple" and "Does Not Contain Apple" instead of 1 and 0 for better readability.

3. Fill Down the Formula

Drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to all relevant rows in your data set.

4. Sort Your Data

Select your entire data range (including the original column and the new helper column). Go to the 'Data' tab, click 'Sort', and choose your helper column as the primary sort key. Sort it in 'Descending' order if you used 1 for matches (so matches appear first), or 'Ascending' if you want non-matches first.

=IF(ISNUMBER(SEARCH("keyword",A2)),1,0)

Excel formula for case-insensitive keyword detection in a helper column.

Method 2: Using Custom Sort Lists for Specific Order

If you need to sort by multiple keywords in a very specific, predefined order (e.g., "Apple" first, then "Banana", then "Cherry"), a helper column combined with a custom sort list can be highly effective. This method assigns a numerical rank to each keyword, allowing for precise sorting.

1. Create a Helper Column with Nested IFs

In your helper column, use a nested IF statement to assign a rank based on the presence of each keyword. For example, if you want "Apple" to sort first (rank 1), "Banana" second (rank 2), and anything else last (rank 3), use a formula like:

=IF(ISNUMBER(SEARCH("Apple",A2)),1,IF(ISNUMBER(SEARCH("Banana",A2)),2,3))

Extend this formula for more keywords, always checking for the highest priority keyword first.

2. Fill Down the Formula

Apply this formula to all rows in your helper column.

3. Sort Your Data

Select your entire data range. Go to 'Data' > 'Sort'. Choose your helper column as the primary sort key and sort in 'Ascending' order. This will arrange your data according to the numerical ranks you assigned.

=IF(ISNUMBER(SEARCH("Apple",A2)),1,IF(ISNUMBER(SEARCH("Banana",A2)),2,IF(ISNUMBER(SEARCH("Cherry",A2)),3,4)))

Nested IF formula for assigning ranks based on multiple keywords.

Workflow for sorting Excel data using a helper column and formulas.

Method 3: Advanced Sorting with VBA (Macro)

For more complex sorting logic, or if you frequently perform this type of sort and want to automate it, a VBA macro offers greater flexibility. This method allows you to define custom sorting rules without needing a visible helper column.

1. Open VBA Editor

Press Alt + F11 to open the VBA editor.

2. Insert a New Module

In the VBA editor, go to Insert > Module.

3. Paste the VBA Code

Paste the following VBA code into the module. This example sorts a range (e.g., A1:B10) based on whether column A contains "Apple", prioritizing those that do.

4. Modify and Run the Macro

Adjust the Set Rng and the keyword "Apple" as needed. You can run the macro by pressing F5 while in the VBA editor, or by going to Developer > Macros in Excel and selecting SortByKeyword.

Sub SortByKeyword()
    Dim ws As Worksheet
    Dim Rng As Range
    Dim LastRow As Long
    Dim Keyword As String
    Dim i As Long
    Dim arr() As Variant
    Dim temp As Variant

    Set ws = ThisWorkbook.ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set Rng = ws.Range("A1:B" & LastRow) ' Adjust your range here
    Keyword = "Apple" ' The keyword to sort by

    ' Add a temporary helper column for sorting
    ws.Cells(1, Rng.Columns.Count + 1).Value = "Helper"
    For i = 2 To LastRow
        If InStr(1, ws.Cells(i, "A").Value, Keyword, vbTextCompare) > 0 Then
            ws.Cells(i, Rng.Columns.Count + 1).Value = 1 ' Contains keyword
        Else
            ws.Cells(i, Rng.Columns.Count + 1).Value = 2 ' Does not contain keyword
        End If
    Next i

    ' Sort the range based on the helper column
    Rng.CurrentRegion.Sort Key1:=ws.Cells(1, Rng.Columns.Count + 1), Order1:=xlAscending, Header:=xlYes

    ' Remove the helper column
    ws.Columns(Rng.Columns.Count + 1).Delete

    MsgBox "Sort complete!"
End Sub

VBA macro to sort an Excel column by a specific keyword, using a temporary helper column.