Show specific text based on value in Excel
Supposing, you have a list of values, now, you would like to show a specific text based on the value. For example, if cell value is greater than 1000, then “Increase” text is displayed in another column as below screenshot shown. To deal with this task, both the REPT and IF functions can do you a favor.
- Show specific text based on value with REPT function
- Show specific text based on value with IF function
In Excel, the normal REPT function is used to repeat the characters a specified number of times. But, here, I will use the REPT function to display specified text based on cell value. The generic syntax is:
- message: The specific text that you want to be displayed.
- logical test: The logical criteria to display the text based on.
1. Please enter or copy the below formula into a blank cell:
Note: In the above formula, “Increase” is the specific text you want to display, “A2 > 1000” is the criteria to be based on, you can change them to your need.
2. Then, drag the fill handle down to the cells that you want to apply this formula, and the specific text will be displayed if meet the criteria, see screenshot:
Tips: This RETP function also can be applied for multiple criteria, if you want to use OR logic, you should combine multiple REPT functions with the & character, and the AND logic can be expressed with the * character.
For example, to display the text “Increase” if the cell value is greater than 1000, if the value is between 500 and 1000, “Normal” text is displayed, otherwise, a “Decrease” text is displayed. Please copy the below formula into a blank cell:
And then, copy the formula to other cells you want to apply this formula, and you will get the result as following screenshot shown:
In Excel, the IF function also can help you to solve this task. The generic syntax is:
- logical_test: It is the condition that you want to test.
- value_if_true: A specific value that you want returned if the logical_test result is TRUE.
- value_if_false: A value that you want returned if the logical_test result is FALSE.
Type or copy the below formula into a blank cell, and then drag the fill handle down to the cells you want to use this formula, and the specific text will be displayed based on the criteria you designed.
Tips: For more criteria, please apply the nested IF function:
Relative functions used:
- The REPT function is used to repeat the characters a specified number of times.
- The IF function is used to test for a specific condition, returns the corresponding value that you supply for TRUE or FALSE.
- Check If Cell Equals Any Value In List
- For example, I have a list of text strings in column A, and now, I want to check if cell value in this column equals any value in another list of column E. If equals, display TRUE, otherwise, a FALSE value is displayed as below screenshot shown.
- If cell contains text then display in Excel
- If you have a list of text strings in column A, and a row of keywords, now, you need to check if the keywords are appear in the text string. If the keywords appear in the cell, displaying it, if not, blank cell is displayed as following screenshot shown.
- Count Keywords Cell Contains Based On A List
- If you want to count the number of keywords appears in a cell based on a list of cells, the combination of the SUMPRODUCT, ISNUMBER and SEARCH functions may help you to solve this problem in Excel.