One of the most predominant common tasks in Excel is checking whether a cell contains a value of interest, and the excel formula if cell contains text then return value in another cell
Several variations of the “If a cell contains” formula in Excel will depend on exactly what values you want to find. Generally, you will use the IF function option to do a logical test and return one value when one of the conditions is met (cell contains) and another value when the desired condition is not met (cell does not include). The below consists of the most frequent scenarios.
Table of Contents
- 1 Ways To Use ‘Excel Formula If Cell Contains Text then Return Value in Another Cell’
- 1.1 Excel Formula If Column Contains Value
- 1.2 Excel Formula If Cell Contains Text
- 1.3 Excel Formula If Cell Contains A Number
- 1.4 The Excel Formula If Cell Contains Specific Text
- 1.5 The Excel Formula If Cell Does Not Contain Specific Text
- 1.6 The Case-Sensitive Formula If Cell Contains Text
- 1.7 Excel Formula for ‘If Cell Contains Text then Return Value In Another Cell’
- 1.8 Excel Formula For ‘If The Cell Contains Specific Text, Copy it to Another Column’
- 2 FAQs
- 2.1 How to return a value in another cell if a cell contains certain text in Excel?
- 2.2 How to return a cell value based on another cell value?
- 2.3 Can Excel return a value that is based on another cell?
- 2.4 How do I display specific text based on text in another cell?
- 2.5 How to automatically enter the value based on another cell?
- 2.6 How do I autofill text in Excel based on another cell?
- 3 Conclusion
Ways To Use ‘Excel Formula If Cell Contains Text then Return Value in Another Cell’
Here are a few ways in which you can fix the issue.
Method #0 Use Advanced System Repair Pro (Strongly Recommended)
- Download this PC Repair Tool rated as "Excellent" on TrustPilot.com.
- Click on Start Scan button to find errors related to Windows.
- Click Repair All to fix all issues with Patented Technologies.
Excel Formula If Column Contains Value
Let us see how to find cells containing anything, any text, number, or date. For this, we can use a quite simple formula that’s for non-blank cells.
For example, to return “Not blank” in the desired column B, if column A’s cell in the same row contains any random value, you enter the following formula in B2 and then double click the small green icon in the lower-right corner to copy the formula down the column.
And the issue excels if the cell contains text, then, it can rectify the return value in another cell.
Excel Formula If Cell Contains Text
If the column contains values, you want to find only cells with text values, excluding the numbers and dates, and you can use the IF in combination with the ISTEXT function. Here’s the respected formula to return some value in another cell if a target cell contains any text.
For instance, you want to insert the word “yes” in column B if a cell in column A contains the text. To have the work done, but the following formula B2.
Excel Formula If Cell Contains A Number
As referred to in the previous method, you can identify cells with numeric values ( like dates and numbers). For this process, use the IF function together with ISNUMBER.
The respective formula returns “yes” in column B if the respected column A contains any number. Read this article, if you want to know how to remove compatibility mode in excel.
See Also: 4 Ways To Fix No Internet Secured Error
The Excel Formula If Cell Contains Specific Text
Finding the cells containing particular text (numbers or dates) is relatively easy. You write a regular IF Formula whether a target cell contains the respected text, and type the reader to return in the value_if_true argument.
For instance, to determine if cell A2 contains “apples,” use this desired formula.
=IF(A2=”apples”, “Yes”, “”)
The Excel Formula If Cell Does Not Contain Specific Text
This particular way is opposite to the previous method (i.e.) returns some value to another column if the target cell does not contain the specified text (“apples”). You can do any of the below.
Insert an empty string (“”) in the value_if_arguement, and text to return in the value_if_false argument:
=IF(A2=”apples”, “”, “Not apples”)
Or, you can also put the “not equal to” operator in logical_test and text to return in value_if_true.
=IF(A2<>”apples”, “Not apples”, “”)
And also, the fix for the excel formula, if a cell contains text, then return value in another cell, can indeed be fixed.
The Case-Sensitive Formula If Cell Contains Text
To recognize the formula to show the difference between upper and lower case characters, use the EXACT function that checks whether the two text strings are comparatively equal, including the letter case.
=IF(EXACT(A2,”APPLES”), “Yes”, “”)
You can also insert the model text string in some cells ( for instance, in C1), adjust the cell with the $ sign ($C$1), and comparatively the respected target cell.
Excel Formula for ‘If Cell Contains Text then Return Value In Another Cell’
Suppose we say we have a list of orders in column A and you want to find the orders with a respected identifier, say “A.” The desired formula in excel if cell contains text then return value will complete the task.
Instead of trying the string in the formula, you can input it in a whole different cell (E1), the respective reference of that cell in your formula.
Excel Formula For ‘If The Cell Contains Specific Text, Copy it to Another Column’
If the column contains a value, copy the contents of the desired valid cells somewhere else; supply the address of the evaluated cell (A2) in the Value_if_argument.
Download: Microsoft Excel
How to return a value in another cell if a cell contains certain text in Excel?
excel formula if cell contains text then return value in another cell A1=” My Text To Check” this will ensure the Value of Cell A1 with your respected Text Parameter 2: “My Text To Return” this is the desired value that you want to return in another Cell if it Matches with Cell A1 Text.
How to return a cell value based on another cell value?
You can use the following formula: =IF(A1=B1,C1,””) Note: Cell A1 and Cell B1 are the two cells that are going to be compared, and Cell C1 is the cell value that you want to extract. =IF(C1=”70$”, B1,””) C1=”70$” =IF(C1=”70$”,B1+100,””)
Can Excel return a value that is based on another cell?
In the Lookup_value box, search the cell that contains the specific value you will return value based on; In the Column box, specify the column you will return the matched value got from. You can also enter the column number into the textbox itself directly you need.
How do I display specific text based on text in another cell?
To display the text that is based on another desired cell, you can also use the conditional function IF. The function represents the value in the first argument ('Bottom 50%'). If the condition is satisfied, otherwise, it displays the text 'Top 50%.'
How to automatically enter the value based on another cell?
Select a blank empty cell that you want to auto-populate the corresponding value. Copy and paste the formula into it, and now you press the Enter key on your keyboard. From now, whenever you select a name in the drop-down list, E2 will be auto-populated with a specific score.
How do I autofill text in Excel based on another cell?
Based on your description, we can use a simple IF formula to accomplish this. Enter =IF(A2= 'Red,' 'NA,') in D2 and use Autofill to fill cells in column D. However, you motioned that column E also needs to auto-populate based on column A
In conclusion, in this article, the excel formula if cell contains text then return value in another cell, If Cell Contains Any Value, and other formulas for excel have explained it in detail. You can go through any of the methods and rectify your issues.
Mauro Huculak is a technical writer. His primary focus is to write comprehensive how-tos to help users get the most out of Windows 10 and its many related technologies. He has an IT background with professional certifications from Microsoft, Cisco, and CompTIA, and he’s a recognized member of the Microsoft MVP community.