How to Find a Character in String in Excel

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.

Dataset - Excel Find Character In String

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.

Searching Characters - Excel Find Character In 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)

FIND function to find character in string

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.

Result of FIND function as number

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")

IF-ISNUMBER with FIND function to generate readable result

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.

Result of IF-ISNUMBER with FIND function to generate readable result

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.

All the cell results - Excel Find Character In String

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.

Case Sensitive FIND result Not Found

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)

SEARCH function to find character in string

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.

Result of SEARCH function as number

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")   

IF-ISNUMBER with SEARCH function to generate readable result

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.

Result of IF-ISNUMBER with SEARCH function to generate readable result

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

Write the formula for the rest of the cells.

All the cell results for SEARCH - Excel Find Character In String

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.     

Case-insensitive SEARCH function 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,""))

Get instances of character in string

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.

Get the instances for all strings from dataset
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)

MID function to extract finding character in string

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.

Result of MID formula to extract the character in string

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

Extract the characters from all strings - Excel Find Character In String

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.

Tags:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo