## If cell contains text

The easiest way to check if a cell 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.

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.

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.

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

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

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

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

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

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

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

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

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3. Related articles

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]

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