Sort Cells by Specific word or words

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")
sort cells by specific words1

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

sort cells by specific words3

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

sort cells by specific words2

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

sort cells by specific words4

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

sort cells by specific words5

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

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar