Skip to main content

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

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:

=REPT("message",logical test)
  • 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:

=REPT("Increase",A2>1000)

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:

=REPT("Increase",A2>1000) & REPT("Normal",(A2>500)*(A2<=1000)) & REPT("Decrease",A2<=500)

And then, copy the formula to other cells you want to apply this formula, and you will get the result as following screenshot shown:


Show specific text based on value with IF function

In Excel, the IF function also can help you to solve this task. The generic syntax is:

=IF (logical_test, [value_if_true], [value_if_false])
  • 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.

=IF(A2>1000,"Increase","")

Tips: For more criteria, please apply the nested IF function:

=IF(A2>1000,"Increase",IF(A2>500,"Normal","Decrease"))


Relative functions used:

  • REPT:
  • The REPT function is used to repeat the characters a specified number of times.
  • IF:
  • The IF function is used to test for a specific condition, returns the corresponding value that you supply for TRUE or FALSE.

More articles:

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

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations