This post will guide you how to** sort cells or text values in a column based on a specific word** even if the word is in the text string in the cell. How do I sort cells in a column by the number of a specific word or words in excel.

Table of Contents

## Sort Cells by Specific word or words

Assuming that you want to sort cells by the count number of a specific word “excel” in excel, just do the following steps:

**#1** select one cell next to the source data in a new column as help column. The type the following formula in the formula box.

`=(LEN(B1)-LEN(SUBSTITUTE(B1,"excel","")))/LEN("excel") `

** **

**Note:** If you want to sort cells by two or more specific words, you can use the below formula. For example, the specific words are “excel” and “learning”.

=SUM(--ISNUMBER(SEARCH({"excel",”learning”}, B1)))

**#2** press **Enter** key in your key board, and drag Auto Fill handler over to other cells to apply this formula.

**#3** select the cells in the helper column, and go to **DATA **tab, click **Sort A to Z** command under** Sort&Filter **group.

**#4** check **Expand the selection** in the **Sort Warning** dialog. Then click **Sort** button.

**#5** the source list data has been sorted by the number of “excel” word.

## Related Functions

- Excel Substitute function

The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])…. - Excel ISNUMBER function

The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)… - Excel LEN function

The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:**=**LEN(text)… - Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)… - Excel SEARCH function

The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…

## Leave a Reply

You must be logged in to post a comment.