Hello! I am an Excel training specialist. This time, let’s explore how to extract and utilize text that meets specific conditions in Excel. Please refer to the following 3 examples to understand more easily.
Example 1: Extracting Data Containing Specific Words
One of the most commonly used conditions is extracting data containing specific words. To do this, you can use the FIND or SEARCH functions. For example, let’s assume the following data:
– Name Position
– Hong Gil-dong Deputy
– Kim Cheol-soo Manager
– Park Young-hee Deputy
If you want to extract only the rows containing the word ‘Deputy,’ you can easily filter the data using the FILTER function. You can set conditions like this: =FILTER(A2:B4, SEARCH(“Deputy”, B2:B4)>0).
Example 2: Calculating the Sum of Data Meeting Specific Conditions
The next example is calculating the sum of data that meets specific conditions. For instance, if you have an Excel sheet with income and expenses recorded, and income is represented as positive numbers while expenses are negative, you may want to calculate the separate sums for income and expenses.
In this case, you can easily calculate the sum of data meeting specific conditions using the SUMIF function, like =SUMIF(B2:B4, “Income”, C2:C4) for income and =SUMIF(B2:B4, “Expense”, C2:C4) for expenses.
Example 3: Changing Data Display Format Based on Conditions
The last example is changing the display format of data based on conditions. For example, let’s say you want to display the text ‘Sales Increase’ if the sales volume of a product is above a certain threshold, or ‘Sales Decrease’ if it is below.
You can achieve this by using the IF function, like =IF(B2>100, “Sales Increase”, “Sales Decrease”).
Through these examples, you should now have a better understanding of extracting and utilizing text based on specific conditions in Excel. Learn more tips and functions to enhance your Excel skills!