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.

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