Here is the Excel Formula If Cell Contains Text Then to Return Value in Another Cell. This is the very useful formula when we deal with strings. We can check if a cell contains a string value and write something in another cell or adjacent column. The following examples shows you the different scenarios with formulas to create strings in a new column based on the data in another column.
In this topic:
- Excel Formula to Check a Cell and Return Value in Another Cell
- Tracing of Excel Formula Calculation Steps
- Clear Explanation of Formula for Matching and Returning Values
- Free Excel File to Download and Check the Examples
Excel Formula to Check If a Cell Contains Text Then Return Value in Another Cell
Here is the Excel formula if cell contains text then return value in another cell. Let us say, we have input data in Cell A1 and We want to Return Value in Another Cell B1. Excel formula for this Criteria is:
=IF(A1="My Text To Check", "My Text To Return", "NOT My Text")
This formula will check If Cell Contains Text Then Return Value. In this If formula, we have three parameters. Here is the detailed explanation of these three parts of the above If Formula.
- Parameter 1: A1=”My Text To Check”, this will check the Value of Cell A1 with your required Text. For Example, “My Text To Check”
- Parameter 2: “My Text To Return”, this is the value which you want to return in another Cell if Matches with Cell A1 Text. For Example, “My Text To Return”
- Parameter 3: “NOT My Text”, this is the value which you want to return in another Cell if Not Matches with Cell A1 Text. For Example, “NOT My Text”
You can download this example Formula at the end of this topic.
Tracing of Excel Formula Calculation Steps
So, this Formula is performing total three different calculation steps. Here is how this formula is working:
- Calculations Step 1: The first step is checking If the cell value is matching with the given string or not.
- Calculations Step 2: And the second step is returning another value if matches.
- Calculations Step 3: And the final step is returning another sting if the target value is not matching with the cell value.
Let us trace this formula by each step. The following formulas will show you how to check If a Cell is matching with another Text. And return a value if the result is TRUE. Also return another string if the result is FALSE.
Excel Formula to check If a Cell Contains specific Text
Let us check if the text in the string matching with a given string. We can use simple Excel expression to check this. We can use Equals to (=) operator to check if value is same as a given value.
="Sting 1"="String 2"
The above formula will check if Sting 1 is equals to String 2 or Not. This will return FALSE.
Now, let us Check the Cell Value is matching with a Text or Not. The following formula will check if the Cell Value in A1 is same as “My String”.
=A1="My String"
If the Sting in Cell A1 is “My String”, The above formula will return TRUE, Else FALSE.
Formula to Return a Value Based on a Conditions
We have seen how to check if a Cell value is matching with given string or not. Let us see how to return another string based on the result.
The below example will show you Excel Formula to check If a Cell Contains Text Then Return Value in Another Cell. Let us return the Value in C1. And Check the Cell A1 for required string. We need to use IF formula to compare cell value with a string and return in another Value.
=IF(A1="My String", "My Return Value","")
The above formula will check Cell A1 with “My String” and return another string “My Return Value” in Cell C1 if matches, returns blank (“”) if not matches.
Let us see another Excel Formula to check if Cell A1 is matching with B1, if matches then rerun the Value in C1, if not matches return the Value in D1. Let us enter the below formula in Cell E1.
=IF(A1=B1,C1,D1)
Explanation
The first part of the Excel If formula will check if A1 and A2 are equal or not. And the Second Part is the return value if true, it will return the value in C1 if matches. And the third part is return value if FALSE, it will return the value in D1 if not matches.
Download Example Excel File with Formula
Here is the Example file with Excel Formula to check If a Cell Contains Text Then Return Value in Another Cell. You can download the file and explore the Example Formulas in the Spreadsheet.
Thanks a lot! Very useful excel formula to return value in another cell if cell contains some text, value or number.
Best and Clear Explanation!
Very helpful. Thanks so much for.
Very useful formula to check cell contains criteria. Thanks.
Very usefull excel formula if cell contains text then return value in another cell. Thanks for clear and easy to use formula.
Awesome! Really very helpful.
=IF(G2=”SELF”,D2,G2)
I’m trying to write a formula if cell G2 = “self”, return the value in D2. If it doesn’t equal “self”, return the value in G2. The result is every line is returning the value in G2.
Hello,
Your Data may be having extra spaces and always convert the case. See the example below:
This will remove the additional spaces and return the cell values based on given condition.
Hope this helps!
Everything is very open with a clear explanation with examples. It was really informative. Your site is useful. Thanks for sharing!
Hi,
Is there any formula for ‘If there is any text (not specific text) in a cell, then add 1’, and if there’s no text, then put 0?
Thanks a lot
=IF(TRIM(A1)=””,0,A1+1)
I am looking for a formula which does calculations based on on multiple text value conditions. If a cell contains a specific word it should multiply or divide from one cell to another cell and show result. in some cases it should keep same cell value to result cell.
Here is the formula to check if a Cell (A2) contains a word (“MyWord”) and Multiply B2*C2. Returns Same Cell Value (A2) If not contains.
=IF(E2=”City1″,”Moderate”,(IF(E2=”City2″,”Hot Weather”, “Pleasant”)))
I am using this formula but I need to have this formula for multiple cities, and using if will be a mess please help.
Keep the city names and temperature type in another Range, use the Vlookup to check the string and return required result.
My cell can contain India-1 or india-2 or india-3 or india-4. How to find if the cell contains the text “India” and then return a value in another cell?
Hi RK,
Follow the below methods:
If you wants to check if a cell value is starting with a specific text (India), then use the below formula:
If your text can be anywhere in the cell, use the below formula:
=IF(ISNUMBER(FIND("India",B15,1)),"Found","Not Found")Hope this helps!
Is it possible to point an If scenario at a drop down and based on the drop down choice, return a value from another sheet to a different cell?
Thank you a lot for all your efforts, your quality and experience is very help for Excel users.
What is the formula for checking strings against the previous row?
Example – Row 1 = H234
Row 2 = I123
Row 3 = I123
I want a column to show “TRUE” if data string matches the previous row.
Assuming that you data in Column A, Here the formula goes in Column B :
Enter this formula in B2 and Fill down the formula, It will show ‘TRUE, FALSE’ in Column B.
Trying to return a number if text in cell G7 = one of the following.
if G7 = Bi Weekly I want to return the number 26
if G7 = Semi Monthly I want to return the number 24
if G7 = weekly I want to return the number 52
I can easily write a formula like this ‘=IF(g7=”Semi monthly”, “24”) but as you can see that is only one of three arguments that need to be made. I am hoping you can help me write a formula to return one of the three numbers 24, 26, 52 based on the text semi monthly, Bi Weekly or weekly contained in G7
You can use the below formula when you have small list of items:
=CHOOSE(MATCH(G7,{"Bi Weekly","Semi Monthly","Weekly"},0),26,24,52)It is good idea to create list of values in a separate range (let’s say: D1:E3) and use VLOOKUP, it is easy to make changes and add more items:
Thanks