Shakil Ahmed
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.
