How do you return a value in another cell if a cell contains certain text?

If cell contains textAuthor: Oscar Cronquist Article last updated on October 19, 2021This article demonstrates different formulas based on if a cell contains a given text.Formula i

How do you return a value in another cell if a cell contains certain text?

If cell contains textAuthor: Oscar Cronquist Article last updated on October 19, 2021

If cell contains text 3

This article demonstrates different formulas based on if a cell contains a given text.

Formula in cell C3:=B3=$E$3

The formula shown in the image above in cell C3 returns TRUE or FALSE based on a comparison between cell B3 and cell E3. The equal sign is a logical operator and returns boolean value TRUE or FALSE, it is not case sensitive.

Table of Contents

  1. If cell contains partial text
  2. Explaining formula
  3. If cell contains partial text - hardcoded asterisks
  4. Alternative function - SEARCH function
  5. If cell contains text then return value
  6. If cell contains text then add text in another cell
  7. If cell contains text then sum
  8. If cell contains text add 1
  9. Highlight cell if cell contains text (Link)
  10. Get Excel file

1. If cell contains partial text

If cell contains

The easiest way to check if a cell partially contains a specific text string is, in my opinion, the IF and COUNTIF function combined. The COUNTIF function allows you to count how many times a text string exists in a cell range.

Formula in cell D3:=IF(COUNTIF(B3, C3), TRUE, FALSE)

The asterisk characters let you perform a wildcard match meaning that it matches any sequence of characters. Adding a beginning and ending asterisk to a text string allows you to check if a cell value contains a specific text string.

Back to top

1.1 Explaining formula in cell D3

Step 1 - Check if cell contains text condition

You can't use asterisks with the equal sign to check if a cell contains a given text, however, the COUNTIF function can do that.

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(range,criteria)

COUNTIF(B3, C3)

becomes

COUNTIF("Green, red and blue","*red*")

and returns 1.

Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF(B3, C3), TRUE, FALSE)

becomes

IF(1, TRUE, FALSE)

The logical_test argument requires boolean values or their numerical equivalents. FALSE = 0 (zero), TRUE any number except zero.

Back to top

1.2 If cell contains partial text - hardcoded asterisks

If cell contains text asterisks

Formula in cell D4:=IF(COUNTIF(B4, "*"&C4&"*"), TRUE, FALSE)

You don't have to add asterisks manually to your cell, you can easily build a formula that adds the asterisks automatically, demonstrated in cell D4. The ampersand & character lets you append the asterisks to the text string you want to use.

Note, if a text string is found twice in the same cell the COUNTIF function only returns 1. It counts cells not text strings.

Back to top

1. Alternative function - SEARCH function

The SEARCH function returns the position of the character at which a specific text string is found.Luckily the IF function accepts any number as TRUE except 0 (zero), however, the SEARCH function returns an error #VALUE! if it can't find the text string.

Formula in cell D5:=IF(ISNUMBER(SEARCH(C5, B5)),1,0)

To avoid the error value I use the ISNUMBER function that returns TRUE if a number and FALSE if anything else, also formula errors.

Back to top

2. If cell contains text then return value

If cell contains text return value

The formula in cell C3 checks if cell B3 contains the condition specified in cell E3. It returns the value if TRUE and a blank cell if FALSE.

Formula in cell C3:=IF(COUNTIF(B3, "*"&$E$3&"*"), B3, "")

Back to top

2.1 Explaining formula in cell C3

Step 1 - Concatenate strings

The asterisk is a wildcard character that matches 0 (zero) to any number of characters. The ampersand character concatenates strings in an Excel formula.

"*"&$E$3&"*"

becomes

"*"&"purple"&"*"

and returns "*purple*".

Step 2 - Check if cell contains string

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(range,criteria)

COUNTIF(B3, "*"&$E$3&"*")

becomes

COUNTIF("Green, red and blue", "*purple*")

and returns 0 (zero). Purple is not found in cell B3.

Step 3 - Return value if TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF(B3, "*"&$E$3&"*"), B3, "")

becomes

IF(0, B3, "")

and returns "" in cell C3.

Back to top

3. If cell contains text then add text from another cell

If cell contains text add text

The formula in cell C3 checks if cell B3 contains the condition specified in cell E3. It returns the value concatenated with a value on the same row from column if TRUE and a blank cell if FALSE.

Formula in cell C3:=IF(COUNTIF(B3, "*"&$F$3&"*"), B3&C3, "")

Back to top

3.1 Explaining formula in cell C4

Step 1 - Concatenate strings

The asterisk is a wildcard character that matches 0 (zero) to any number of characters. The ampersand character concatenates strings in an Excel formula.

"*"&$E$3&"*"

becomes

"*"&"purple"&"*"

and returns "*purple*".

Step 2 - Check if cell contains string

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(range,criteria)

COUNTIF(B4, "*"&$E$3&"*")

becomes

COUNTIF("Black, purple, white", "*purple*")

and returns 1. Purple is found in cell B4.

Step 3 - Return concatenated value if TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF(B4, "*"&$E$3&"*"), B4&C4, "")

becomes

IF(0, B4&C4, "")

becomes

IF(0, "Black, purple, white"&"#11", "")

and returns "Black, purple, white#11" in cell D4.

Back to top

4. If cell contains text then sum

If cell contains text sum

The formula in cell F3 checks if cells in column B contain the condition specified in cell E3 and sums the corresponding values in column C.

Formula in cell C3:=SUM(ISNUMBER(SEARCH(E3, B3:B6))*C3:C6)

Back to top

4.1 Explaining formula in cell C4

Step 1 - Search for string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is not a case-sensitive search.

SEARCH(find_text,within_text, [start_num])

SEARCH(E3, B3:B6)

becomes

SEARCH("North", {"North, South"; "South, East"; "West, North, South"; "South, West"})

and returns {1; #VALUE!; 7; #VALUE!}.

Step 2 - Check if value in array is an error

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

ISNUMBER(SEARCH(E3, B3:B6))

becomes

ISNUMBER({1; #VALUE!; 7; #VALUE!})

and returns {TRUE; FALSE; TRUE; FALSE}.

Step 3 - Multiply corresponding numbers

The asterisk lets you multiply numbers in an Excel formula. We are multiplying boolean values in this example.

ISNUMBER(SEARCH(E3, B3:B6))*C3:C6

becomes

{TRUE; FALSE; TRUE; FALSE}*{10; 15; 6; 2}

TRUE equals 1 and FALSE equals 0 (zero).

{TRUE; FALSE; TRUE; FALSE}*{10; 15; 6; 2}

and returns {10; 0; 6; 0}.

Step 4 - Sum numbers

The SUM function adds numbers and returns a total.

SUM(ISNUMBER(SEARCH(E3, B3:B6))*C3:C6)

becomes

SUM({10; 0; 6; 0})

and returns 16 in cell F3.

Back to top

5. If cell contains text add 1

If cell contains text add 1

The formula in cell C3 counts cells containing the given text string in cell E3.

Formula in cell C3:=IF(COUNTIF(B3,"*"&$E$3&"*"),1+MAX($C$2:C2),"")

Back to top

5.1 Explaining formula in cell C3

Step 1 - Concatenate strings

The asterisk is a wildcard character that matches 0 (zero) to any number of characters. The ampersand character concatenates strings in an Excel formula.

"*"&$E$3&"*"

becomes

"*"&"North"&"*"

and returns "*North*".

Step 2 - Check if cell contains given string

TheCOUNTIF functioncalculates the number of cells that is equal to a condition.

COUNTIF(range,criteria)

COUNTIF(B3,"*"&$E$3&"*")

becomes

COUNTIF("North, South", "*North*")

and returns 1.

Step 3 - Calculate count

The MAX function returns the largest number from a cell range or array.

Reference $C$2:C2 contains both absolute and relative cell references which makes it grow when the cell is copied to cells below.

1+MAX($C$2:C2)

becomes

1+0

and returns 1.

Step 4 - Show count if corresponding cell contains given string

TheIF functionreturns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF(B3,"*"&$E$3&"*"),1+MAX($C$2:C2),"")

becomes

IF(C1,1+MAX($C$2:C2),"")

becomes

IF(C1, 1, "")

and returns 1.

Back to top

6. Get Excel *.xlsx file

Get the Excel file


If-cell-containsv2.xlsx

Back to top

Logic category

test

If cell contains text from list
This article demonstrates several ways to check if a cell contains any value based on a list. The first example []

test

If cell contains multiple values
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 []

test

Use IF + COUNTIF to evaluate multiple conditions
The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula []

test

IF function with AND function  multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. []

test

How to simplify nested IF functions
Nested IF statements in a formula are multiplecombined IF functions so more conditions and outcomes become possible. They all are []

test

If cell equals value from list
This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table []

test

If cell contains any text
The picture above shows different values in column B and a formula in column C that tries to identifies the []

test

If cell has value
The easiest way to check if a cell has a value is, in my opinion, to use the equal sign []

Functions in this article
IFCOUNTIFTRUEFALSEISNUMBERSEARCHSUM
More than 600 Excel formulas

Excel categoriesFunctionsFormulasExcel TablesAdvanced FilterData ValidationDrop DownNamed RangesExcel SolverChartsConditonal FormattingPivot TablesVBAMacrosUDFsArchive

Leave a Comment

Leave a ReplyClick here to cancel reply.

Name (required)

Mail (will not be published) (required)

Website

Δ

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Video liên quan