In Excel, we can find a specific character within a string. Several built-in functions help us finding the character. Today we are going to show you how to find a character in a string in Excel. For this session, we are using Excel 2019, feel free to use yours.

First things first, let’s get to know about the dataset that is the base of our examples.

Here, we have a table that contains few familiar proverbs as the string. Using this dataset, we will find a character in a string.

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Find Specific Character in String

For example, we have introduced a column that contains the list of characters we are aiming to find in a string.

Now, let’s find the character within the string in the same row (we are keeping things simple).

### 1. Find Character in String Using FIND Function

In Excel, there are several **TEXT** functions, among the functions a couple of functions are suitable for our task. One of these is **FIND**. The **FIND **function returns the position of a text string inside another.

#### Locating The Position of the character

The **FIND** function returns the position as a number value. You can understand it better by using the function. Let’s write a formula using the **FIND** function

`=FIND(C4,B4)`

We are searching the character, stored in **C4**, within the string in the **B4** cell. This will return the position of the character from the string.

Here we have found the position of the character *w*. The character has occurred first at the 7th position within the string from **B4**.

Numbers may not be understandable for a reader at first sight (also you can get confused opening the file after a while). So, let’s make things more understandable.

#### Using Logical Function for Fast Understandability

Depending on the circumstances different approaches can be performed. For the time being, we are using a couple of logical functions; **IF **and **ISNUMBER**.

**IF** runs a logical test and returns the Boolean value. **ISNUMBER** checks whether a cell contains a numeric value or not, it also returns a Boolean value.

Let’s form the formula using these functions.

`=IF(ISNUMBER(FIND(C4,B4)),"Found","Not Found")`

The **FIND** function is inside **ISNUMBER**, which checks whether **FIND** returns the position or error (when **FIND** doesn’t get the character within the string it returns **#VALUE!** error). For a number (position) it returns **TRUE**

That’s why we have set “Found” as *if_true_value* for the **IF** function.

Here, the character *w *is in the string, so the **FIND** function returned number, evaluating that **ISNUMBER** returned **TRUE** and ultimately we got the result “Found”.

Let’s write the formula for the rest of the cells.

Here, the character we have searched all were with the corresponding cells. That’s why the formula returned “Found”.

The **FIND** function is case-sensitive. The searching character should be exactly in the same form within the string.

If we search for a character written in a different form the result will be changed.

For example, we searched *W* and got the result “Not Found”.

### 2. Find Character in String Using SEARCH Function

Another function we can use to find a character in a string is **SEARCH**. The **SEARCH **function returns the position of a text string inside another.

Seems similar to **FIND**!!! Yes apart from some basic changes **FIND **and **SEARCH** perform the same tasks.

#### Locating The Position of the character

We will get the position of the character (if found) within the string through **SEARCH**.

`=SEARCH(C4,B4)`

We are searching the character, stored in **C4**, within the string in the **B4** cell. This will return the position of the character from the string.

It will return the position for the first *w *within the string. And that’s 7.

#### Using Logical Function for Fast Understandability

The operation of **FIND **and **SEARCH** is similar, so similar readability issues may arise. And we can get rid of the problems using the same **IF** and **ISNUMBER**.

`=IF(ISNUMBER(SEARCH(C4,B4)),"Found","Not Found") `

The **SEARCH** function is inside **ISNUMBER**, which checks whether **SEARCH** returns the position or error. And for a number (position) it returns **TRUE**. We have set “Found” as *if_true_value* and “Not Found” as *if_false_value* for the **IF** function.

The character is in the string and our formula provided the result “Found”.

Write the formula for the rest of the cells.

Unlike **FIND**, the **SEARCH** function is case-insensitive. If we search for an uppercase character that is in lowercase within the string, still we will find “Found” as result. ** **

### Number of Instances of Character in String

Here most of our strings have the character multiple times. To get the number of occurrences of the character we need to use **LEN** and **SUBSTITUTE**.

`=LEN(B4)-LEN(SUBSTITUTE(B4,C4,""))`

Firstly, the **SUBSTITUTE **function removes all of the characters being counted in the source string. Then it subtracted the length of the string (with the character removed) from the length of the source string.

Ultimately the result is the number of characters that were removed with **SUBSTITUTE**.

The formula will provide the number of instances for the rest of the characters and strings.

Extract the Character from a String

Finding the position, or generating “Found” or “Not Found” may always not be the context of the tasks. We may need to extract the character, for that we will use **MID**.

The **MID **function extracts a given number of characters from the middle of a provided string.

The formula will be the following one

`=MID(B4,SEARCH(C4,B4),1)`

Provided the string in **MID** and to let Excel know the position where to start we used **SEARCH **that returned the position of the character.

Similarly, fetch the characters from the rest of the strings using the formula.

## Conclusion

That’s all for the session. We have listed approaches to find a character in a string in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.