How to sort cells containing specific text in Excel

The merits of access are endless. Excel is undoubtedly a very powerful tool for organizing data and filtering them. There are many tools that help you sort and filter data in Excel. You must have run across times when you need to sort cells containing specific words and then pile them up together. For example, you may sometimes need to sort a list of people by their last names. If you wonder how to do it, here’s an easy trick.

Sorting cells in by specific words contained in them can be done easily through conditional formatting and the filter option in Microsoft Excel 2007. First, select the column that you want to sort the data for. Now click on Conditional Formatting under the “Home” tab. You will see a handful of options for formatting cells. Under conditional formatting, go to Highlight Cell Rules>Text That Contains. Type the word or phrase that you want to sort the column by. As you type, you’ll see the cells containing the word get highlighted. Choose a color(Red by default). Finally click on OK. Quickly scroll upto the bottom of your  sheet and you’ll see that the cells that contain the word have been highlighted.

Now select the first cell of the column and click on the Data tab. Next, click on the Filter button. You will now see a dropdown on the first cell of the column. Click on the dropdown and select Sort by Color>Sort by Cell Color and choose the select the color that you used for highlighting. You can also select the sort by font color option. Now, all the cells containing the specific word get piled up on top of the rest. If you want to sort other cells in a similar way, you can proceed as above. Once you have sorted, you can clear the formatting rules from the Conditional formatting dropdown.