## If cell contains text

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

Formula in cell C3:

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

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:

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.

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

### 1.2 If cell contains partial text - hardcoded asterisks

Formula in cell D4:

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.

### 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:

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

## 2. If cell contains text then 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:

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

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

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:

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

## 4. If cell contains text then 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:

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

## 5. 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:

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

The COUNTIF function calculates 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

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.

## 6. Get Excel *.xlsx file

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

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 […]

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to simplify nested IF functions

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

Extract unique distinct values if value contains string

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

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 […]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3. Weekly Blog EMAIL […]

How to simplify nested IF functions

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

How to use the SEARCH function

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

### Leave a Reply

### 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 < and > becomes >

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