Filtering becomes indispensable when you have a larger and more complicated dataset. Retrieving the desired data is quite time-consuming from such a dataset. Furthermore, if you have multiple tags in one cell then it becomes more difficult to find particular data in Excel. Thus, you should know how to use filtering when you have multiple tags in one cell in Excel.
In this article, I’ll discuss the methods of how to use multiple tags in one cell in Excel.
How to Use Multiple Tags in One Cell in Excel: 4 Methods
Here, I will demonstrate 4 suitable methods with detailed steps on how to use multiple tags in one cell in Excel. For your better understanding, I am going to use the following dataset. Which contains three columns. Those are the Name of the Employee, Employee ID, and Tags. Furthermore, in the Tags column, there are three types of information about the employees. The dataset is given below.
Among those employees, let’s find out who are managers only. In addition, for conducting the session, I’m using Microsoft 365 version. So, let’s get started.
1. Use of Text Filters for Multiple Tags in One Cell
The AutoFilter option in Excel is used as an embedded button to filter out various types of required data in a data range or column.
So, if you want to find the “Employee Name” who are managers, you can do that in the following way.
- Firstly, select any cell from the dataset and press CTRL+SHIFT+L.
As a result, you will see the drop-down arrow beside every column head.
- Then, click on the drop-down arrow of the “Tags” field.
- After that, go to the Text Filters menu.
- Then, choose the Custom Filter option.
At this time, a new dialog box named Custom Autofilter will appear.
- Firstly, choose contains from the 1st drop-down arrow of the Custom AutoFilter dialog box.
- Secondly, write Manager in the second space.
- Finally, press OK.
As a result, you will see only the employee information who are managers. Also, the drop-down arrow of the Tags column becomes a Filtered icon.
Read More: How to Add Tags in Excel
2. Applying Advanced Filter in Excel
In the previous method, you see the application of Text Filters to find that particular tag. Now, I will use the Advanced Filter option, where you don’t need to bring the Filter icon.
Now, I want to find the “Employee Name” who are managers. So, let’s see the steps below.
Steps:
- First, write down the criteria including that particular column header from where your criteria came. Here, you must write the criteria below the column header. Furthermore, for a text, you must use two Asterisks within the criteria.
As an example, see the following image. Where I have written Tags >> *Manager* in the C19:C20 cells.
- After that, select any of the cells from the dataset.
- Then, open the Advanced Filter option by clicking the Data tab > Sort & Filter > Advanced.
As a result, you will see a new dialog box named Advanced Filter.
- Later, specify the range of your whole dataset from where you want to filter in the List range option and provide the criteria in the Criteria range.
- Furthermore, if you don’t need similar data, check the box close to Unique records only.
- Subsequently, press OK.
Finally, you’ll see the following output.
Read More: How to Add Tag to Properties in Excel
3. Using SEARCH Function to Use Multiple Tags in One Cell
You can apply the SEARCH function in Excel to find out particular information from tags when you have multiple tags in one cell. Actually, the SEARCH function will return the number of characters including spaces and the first letter of that particular text from which the text is found. Now, let’s see the steps given below.
Steps:
- Firstly, you have to select a new cell E5 where you want to keep the search result.
- Secondly, you should use the formula given below in the E5 cell.
=SEARCH("Manager",D5:D17,1)Here, the finding text is “Manager”. Then, the text range is D5:D17 where should search the text “Manager”. And, 1 is the starting number from which character the search will be started.
- Then, press ENTER.
Lastly, you will find the following result. Where a numeric number denotes that the cell contains the manager word.
Read More: How to Filter Tags in Excel
4. Employing Combined Functions for Multiple Tags
You can employ a combination of some functions like IF, ISNUMBER, and SEARCH function to find out particular information from tags when you have multiple tags in one cell. Basically, in the previous method, I used only the SEARCH function, but the finding status was a little clumsy. Thus, to make a clear search result, I’m going to use another formula using IF, ISNUMBER, and SEARCH functions. Furthermore, you can modify these functions for different types of results according to your preference. The steps are given below.
Steps:
- Firstly, you have to select a new cell E5 where you want to keep the search result.
- Secondly, you should use the formula given below in the E5 cell.
=IF(ISNUMBER(SEARCH("Manager",D5:D17,1)),"Found","Not Found")
- Consequently, press ENTER.
Formula Breakdown
- Here, the SEARCH function will search for the text “Manager” within the range D5:D17.
- Output: {8;#VALUE!;#VALUE!;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}.
- Then, the ISNUMBER function will check whether the cell value is a number or not.
- Output: {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- Lastly, the IF function will check the same logical test. When the logical test becomes TRUE then it will return Found. Otherwise, it will return Not Found. Here, Inverted Comma is a must for getting a text as the output.
Lastly, you will get the perfect search result.
💬 Things to Remember
- Here, method 1 (using Text Filters) is the easiest and most useful one.
- Furthermore, if you want to clear the filter option then select any cell within filtered data and just press CTRL+SHIFT+L.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 suitable methods to use Multiple Tags in One Cell in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- Smart Tags in Excel: Definition & Different Uses
- How to Make Name Tags in Excel
- How to Create Price Tags in Excel
<< Go Back to Tags in Excel | Data Analysis with Excel | Learn Excel
