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 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”

Excel if cell contains text then return value in another cell

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.

Excel Formula If Cell Contains Text Then Return Value in Another Cell

Excel Formula If Cell Contains Text Then Return Value in Another Cell

Share This Story, Choose Your Platform!

39 Comments

  1. Guru September 20, 2018 at 8:16 am - Reply

    Thanks a lot! Very useful excel formula to return value in another cell if cell contains some text, value or number.

  2. Tom September 24, 2018 at 8:17 am - Reply

    Best and Clear Explanation!

  3. Ben November 3, 2018 at 6:32 am - Reply

    Very helpful. Thanks so much for.

  4. james April 6, 2019 at 4:23 pm - Reply

    Very useful formula to check cell contains criteria. Thanks.

  5. Neel July 11, 2019 at 6:18 pm - Reply

    Very usefull excel formula if cell contains text then return value in another cell. Thanks for clear and easy to use formula.

  6. John August 5, 2019 at 5:55 pm - Reply

    Awesome! Really very helpful.

  7. Kim August 7, 2019 at 1:30 pm - Reply

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

    • PNRao August 9, 2019 at 8:03 am - Reply

      Hello,
      Your Data may be having extra spaces and always convert the case. See the example below:

      =IF(UPPER(TRIM(G2))=”SELF”,D2,G2)

      This will remove the additional spaces and return the cell values based on given condition.

      Hope this helps!

  8. Mike August 17, 2019 at 4:37 am - Reply

    Everything is very open with a clear explanation with examples. It was really informative. Your site is useful. Thanks for sharing!

  9. Julián May 5, 2022 at 9:19 am - Reply

    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

    • PNRao June 20, 2022 at 12:24 pm - Reply

      =IF(TRIM(A1)=””,0,A1+1)

  10. Denzil July 11, 2022 at 5:50 am - Reply

    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.

    • PNRao July 12, 2022 at 4:05 am - Reply

      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(SEARCH(“MyWord”,A2)>0,B2*C2,A2)
  11. Faizan July 25, 2022 at 10:12 am - Reply

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

    • PNRao September 11, 2022 at 6:47 am - Reply

      Keep the city names and temperature type in another Range, use the Vlookup to check the string and return required result.

  12. S Ramakrishnan October 24, 2022 at 4:47 pm - Reply

    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?

    • PNRao October 26, 2022 at 11:17 am - Reply

      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(LEFT(B15,5)="India","Found","Not Found")
      

      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!

  13. Rebecca October 27, 2022 at 12:45 am - Reply

    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?

  14. Sigma December 9, 2022 at 3:08 am - Reply

    Thank you a lot for all your efforts, your quality and experience is very help for Excel users.

  15. Janet Fix February 5, 2023 at 6:57 pm - Reply

    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.

    • PNRao February 27, 2023 at 2:44 am - Reply

      Assuming that you data in Column A, Here the formula goes in Column B :

      =A2=A1
      

      Enter this formula in B2 and Fill down the formula, It will show ‘TRUE, FALSE’ in Column B.

  16. Doug February 9, 2023 at 3:06 am - Reply

    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

    • PNRao February 27, 2023 at 3:06 am - Reply

      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:

      =VLOOKUP(G7,D1:E3,2,FALSE)
      

      Thanks

  17. Vanessa July 24, 2023 at 4:45 pm - Reply

    Hi, is there a formula that could look in sheet 2 column B and if it finds the same text as sheet 1 A1 then it returns the contents of the cell in sheet 2 column D in the same row it found it?
    I was looking at an IF THEN function but the second part is proving difficult! Any help appreciated!

    • PNRao July 27, 2023 at 8:35 am - Reply

      Hi, You can use the following formula to check if the Sheet B Column Cell contains same data in the Sheet1 A1 and display in the same row in the Sheet1.

      =IF(Sheet2!B2=Sheet1!$A$1,Sheet2!D2,"")
      
  18. Giles October 14, 2023 at 2:50 pm - Reply

    I need a formula to lookup a value in B3 that appears multiple times in the range (B3:B520) and then gives me the value of the cell directly above each matching B3 value it finds – can that be easily done?

  19. Kayla February 2, 2024 at 5:19 pm - Reply

    I’m trying to figure out a formula that if G=1 in a row, it takes the value in e for that same row and adds it to P15.

  20. Louis March 11, 2024 at 11:46 am - Reply

    Hello,
    Is there a formula that if column A contains a certain word, then column C would show the value of column B?
    Thank you in advance

    • PNRao March 28, 2024 at 1:33 pm - Reply
      C1=IF(A1="CertainWord",B1,"")
      
      Hope this helps!
      Thanks
      PNRao!
  21. Adrian April 8, 2024 at 3:02 pm - Reply

    Looking to see if there is any value or text in one cell. If so, the value of a different cell should then be a percentage of a third cell e.g. If cell A2 has any yext or numbers at all, A£ should be the value of A1 divided by 4.

  22. Greg April 11, 2024 at 11:10 pm - Reply

    Hello,

    I’m trying to writ a formula that If one sell =”Win” then it populates a $value from another cell, and if it = “loss” it populates with $0. can you please help

    • PNRao April 21, 2024 at 7:23 am - Reply

      Here is the Formula:

      =IF(A1="Win",B1,IF(A1="Loss",0,""))
      

      This will check if A1=”Win”, then populates the Value in the B1. if A1=”Loss”, then populates 0, or else leave it blank,

  23. Nyx May 19, 2024 at 7:03 pm - Reply

    Hi, I have 3 text columns: C,D,E in sheet 1. I have the same columns in sheet 2. Please can you help with the formula where I want to say, if the C,D,E text columns in both sheets have the same text then populate the text from column F from sheet 2?

    Would appreciate your help

    • PNRao May 20, 2024 at 4:01 am - Reply

      The below code will check the same rows and columns. If you need in any row match, then use the vlookup.

      =IF(AND(C1=Sheet2!C1,Sheet1!D1=Sheet2!D1,Sheet1!E1=Sheet2!E1),Sheet2!F1,"Not Found")
      
  24. Gayan June 13, 2024 at 3:13 pm - Reply

    I use VLOOKUP to extract dates from Sheet A to a table in Sheet B, in this column with dates, some cells contain a “specific text” (when dates are unavailable), and I want the formula to lookup a different column in Sheet A if the cells in Sheet B contains this “specific text”. Any ideas would appreciate a lot!

    • PNRao June 13, 2024 at 3:30 pm - Reply

      You can check if the initial VLOOKUP result matches to a specific text, then look into different column, else return the same initial VLOOKUP value.

      =IF(VLOOKUP(A2, SheetA!$A:$D, 4, FALSE)="Specific Text", VLOOKUP(A2, SheetA!$B:$D, 3, FALSE), VLOOKUP(A2, SheetA!$A:$D, 4, FALSE))
      

      This formula checks if the first VLOOKUP (searches A:D in SheetA, column 4) finds “Specific Text”. If yes, it uses another VLOOKUP (searches B:D in SheetA, column 3) – else, it uses the original result.

      This example checks for A2 value in Column A, then returns the Column:D value [VLOOKUP(A2, SheetA!$A:$D, 4, FALSE)], if Column:D value matches ‘Specific Text’ [VLOOKUP(A2, SheetA!$A:$D, 4, FALSE)=”Specific Text”] then it will check for A2 value in Column:B and Returns Column:D Value [VLOOKUP(A2, SheetA!$B:$D, 3, FALSE)]

      Hope this helps!

  25. Ramy Jo June 26, 2024 at 2:01 pm - Reply

    Need help with a formula. I have a list of 2,544 numbers in Column A. In columns D, G, and J I have text that includes the number(s) in column A. I need to match them up. Can’t figure out what kind of formula would work. I tried match but since other text is in the other cell along with the number it wouldn’t work. Here’s an example of what I’m working with….

    Column A Column D Column G
    39655 Limited access 44550 test In tolerance test 39655
    44550 Failed test 65812
    65812

    I want to put the info in column A in blank column right beside the cell it’s in in the corresponding columns without having to manully do it one at a time

    Thanks for any and all help!

  26. kerry July 12, 2024 at 6:05 pm - Reply

    AUTHOR, or anyone who might know the answer….Need help with proper syntax, please! I am on the right track with an “IFS” command where I am trying to define the output if the input falls within certain ranges. The beginning was easy as the values were only in the range of 1 or 2 whole numbers and I could define the output easily. However I am getting to where the range is getting much wider and entering the if/then with each incremental number is becoming too much. I need to be able to tell Excel to not just to equal a number but to fall within a range of numbers. For example, here is my formula thus far:

    =IFS($G$3<10,"ALL",$G$3=10,"9",$G$3=11,"10",$G$3=12,"11",$G$3=13,"11",$G$3=14,"12",$G$3=15,"12",$G$3=16,"13",$G$3=17,"13",$G$3=18,"14",$G$3=19,"13",$G$3=20,"14",$G$3=21,"15")

    You can see where my last condition is for the input value of 21 that yields 15. However, I need to write it as any value 21 thru 24 that yields an output of 15, then as any value between 25 to 29 yields an output of 16, then as any value between 30 to 35 yields output of 17, 36 to 44 yields 18, and so on…In other words, I am trying to move away from defining each individual whole number as I have done so far and condense the input range to yield the same output value. Is there a way to define what it is I am trying to express in shorter abbreviated syntax??? I tried to use a colon between the minimum and maximum values (example 25:29) but that is wrong. And putting a dash (25-29) tells excel I want to subtract, I think, so that's not correct either….Is what I am trying to ask making any sense?

    Basically I am taking a table that for any specific input value RANGE there is a single output value I am trying to define. Last example: if our company receives 36 to 44 of a lot size then we need to sample and test 18 of those for conformity, OR, If we receive 119 to 233 of the same lot size, then we need to test 22 samples, so on and so forth…So basically, whether I type into cell $G$3 the number 119, 145, 156, or 233, then I need the output value to display 22. Any value between 234 and 2536 I need the output to be 23.

    Is there a way to accomplish this in Excel? Hope I didn't confuse everyone….

    • PNRao July 14, 2024 at 4:11 am - Reply

      You can opt for best one which suites your requirement.
      Fixed Ranges: If the ranges are fixed then use: IFS Function as shown in the below Formula:

      =IFS(
        $G$3 < 10, "ALL",
        $G$3 = 10, "9",
        $G$3 = 11, "10",
        $G$3 = 12, "11",
        $G$3 = 13, "11",
        $G$3 = 14, "12",
        $G$3 = 15, "12",
        $G$3 = 16, "13",
        $G$3 = 17, "13",
        $G$3 = 18, "14",
        $G$3 = 19, "13",
        $G$3 = 20, "14",
        AND($G$3 >= 21, $G$3 <= 24), "15",
        AND($G$3 >= 25, $G$3 <= 29), "16",
        AND($G$3 >= 30, $G$3 <= 35), "17",
        AND($G$3 >= 36, $G$3 <= 44), "18",
        AND($G$3 >= 119, $G$3 <= 233), "22",
        AND($G$3 >= 234, $G$3 <= 2536), "23"
      )
      

      Ranges My Change in Future: If your ranges changes in future and may not be fixed in future, then create a sheet (Example: Calculations) and use the VLOOKUP function to get the respective value.

      =VLOOKUP($G$3,Calculations!$A$2:$B$14, 2,TRUE)
      

      Hope this helps!

Leave A Comment