How to Find Character in String Excel (8 Easy Ways)

For computers and electronics devices characters are represented by a standard numbering system. ASCII is one of the numbering systems widely used. Every character is represented by a number. In this article, we find character in string using Excel’s Find Feature, multiple functions such as FIND, SEARCH, ISNUMBER, and IF as well as VBA Macro Code.

Suppose we want to find specific characters from Employee’s Name and ID. We have to find characters appearing in the Character Sign column. For better understanding, we’re showing standard Character Numbers in another column for the characters we’re about to find.

Dataset-Find Character in String Excel

Dataset for Download

8 Easy Ways to Find Character in String Excel

Method 1: Using FIND Function

We can use the FIND function to find a specific character to want. The syntax of the FIND function is

=FIND (find_text, within_text, [start_num])

Inside the formula,

find_text; declares the text to be found.

within_text; declares the text where the find_text to be found.

[start_num]; the beginning position in the within_text (optional), default position is 1.

Step 1: Insert the following formula in any cell (i.e., E4).

=FIND(C4,B4)

In the formula,

C4; is the find_text.

B4: is the within_text.

We use start_num position as Default.

Find function-Find Character in String Excel

Step 2: Hit ENTER and Drag the Fill Handle. The positions of the desired characters will appear in the cells.

Find function final result-Find Character in String Excel

You can find any character we wish to.

Read more: How to Find a Character in String in Excel

Method 2: Using SEARCH Function

The SEARCH function also works as the FIND function. The syntax of the SEARCH function is

 =SEARCH (find_text, within_text, [start_num])

In the formula,

find_text; declares the text to be found.

within_text; declares the text where the find_text to be found.

[start_num]; the beginning position in the within_text (optional),default position is 1.

Step 1: Type the following formula in any cell (i.e., E4).

=SEARCH(C4,B4)

In the formula,

C4; is the find_text.

B4: is the within_text.

We use start_num position as Default.

Search function-Find Character in String Excel

Step 2: Press ENTER and Drag the Fill Handle to bring up the character’s position of all the strings.

Search function final result-Find Character in String Excel

You can find any of the characters using the SEARCH function.

Read more: Excel Search for Text in Range (11 Quick Methods)

Method 3: Using ISNUMBER and FIND Function

We can test any string for any look-up characters present in the string. In this case, we can use a combination of ISNUMBER and FIND functions to find out any specific character whether presented in the string or not. The ISNUMBER function returns TRUE or FALSE text depending on the numeric or not-numeric cell values. The syntax of the ISNUMBER function is

=ISNUMBER(value)

Here, in the formula,

value; have to be a numeric value otherwise ISNUMBER formula results “FALSE” in text.

Step 1: Write the following formula in any blank cell (i.e., E4).

=ISNUMBER(FIND(C4,B4))

Here,

FIND(C4,B4); is defined as the value.

isnumber and find function

Step 2: Press ENTER and Drag the Fill Handle to bring up the character status that declares specific character is present in the specific cells or not.

isnumber and find function result

In the character status “TRUE” means that the desired character (in column C) is present in the specific text string.

Method 4: Using ISNUMBER and SEARCH Function

Similar to Method 3, a combination of ISNUMBER and SEARCH function can bring up the character status as TRUE or FALSE.

Step 1: Paste the following formula in any blank cell (i.e., E4).

=ISNUMBER(SEARCH(C4,B4))

The formula declares the same argument as it does in the previous method (i.e., Method 3).

isnumber and search function-Find Character in String Excel

Step 2: Hit ENTER and Drag the Fill Handle. It shows the desired characters (appearing in column C) are present or not in the cells by TRUE or FALSE text respectively.

isnumber and search function final result-Find Character in String Excel


Similar Readings:


Method 5: Using Find Feature

Excel offers features like Find to find any specific characters in worksheets or workbooks.

Step 1: Go to Home Tab > Select Find & Select (in Editing section) > Choose Find (from the options).

find feature-Find Character in String Excel

Step 2: A Find and Replace window opens up. In that window’s Find section, Type any character you want to find (i.e., a).

Other setting in the Find section is Default. You can change them regarding your search or data types.

Click on Find Next.

Find and replace window

Step 3: “a” existing in the cells begins to be marked with Green Rectangular every time we click on Find Next. You can substitute the character “a” with any other characters we wish.

find feature final result-Find Character in String Excel

You can use CTRL+H to bring up the Find and Replace window.

You can click on Find All to bring up all the cells containing your desired character. However, it’s quite difficult to find out the specific cells containing specific characters using Find All as it comes up with a list below the Find and Replace window.

Method 6: Using IF ISNUMBER and FIND Function

The syntax of the IF function is

 =IF (logical_test, [value_if_true], [value_if_false])

The IF function needs a logical_text to test a cell reference then results in specific texts we set.  We can use the combination of ISNUMBER and FIND function as a logical_text.

Step 1: Type the following formula in any cell (i.e., E4).

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

Inside the formula,

ISNUMBER(FIND(C4,B4); is the logical_test.

“Found”; is the value that appears if the logical_text is TRUE.

“Not Found”; is the value that appears if the logical_text is FALSE.

if isnumber and find

Step 2: Press ENTER and Drag the Fill Handle. If the desired character exists in the specific cell, the formula results in “Found” otherwise “Not Found”.

if isnumber and find result

To keep things simple, we demonstrate the characters that exist in the cells. You can use various characters that are already existing in the cells or not.

Method 7: Using IF ISNUMBER and SEARCH Function

We can use the SEARCH function in the place of the FIND function. Because both functions SEARCH and FIND are quite similar in their outcomes. In this case, the combination of IF, ISNUMBER, and SEARCH function defines the same arguments as it does in Method 6.

Step 1: Paste the following formula in any blank cell (i.e., E4).

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

The formula contains all the arguments similar to Method 6.

if isnumber and search function-Find Character in String Excel

Step 2: Hit ENTER then Drag the Fill Handle. It results in the resultant texts “Found” or “Not Found” depending on their logical_test.

if isnumber and search function final result-Find Character in String Excel

Method 8: Using VBA Macro Code

We can generate a custom function using VBA Macro Code. In this case, we generate a custom function named FindM to find the nth occurrence of any character in a string.

Step 1: Press ALT+F11 altogether. Microsoft Visual Basic window opens up. In the window, From Toolbar, Select Insert > Choose Module.

vba macro code-Find Character in String Excel

Step 2: In the Module, Paste the following Macro Code.

Function FindM(mFindWhat As String, _
mInputString As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindM = 0
For J = 1 To N
FindM = InStr(FindM + 1, mInputString, mFindWhat)
If FindM = 0 Then Exit For
Next
End Function

macro code

The code constitutes a custom function FindM which we create with a syntax of

=FindM(find_text,within_text,occurence_num)

 Here,

 Occurenece_num; is the occurring times any character occurs in the within_text.

 Step 3: Back to the worksheet. Type the following formula in any blank cell (i.e., E4).

=FindM(C4,B4,1)

inside the formula,

C4; is the find_text.

B4; is the within_text.

1; is the occurrence_num.

Step 4: Hit ENTER afterward Drag the Fill Handle. All the character positions appear in the cells like the image below.

vba macro code final result-Find Character in String Excel

Conclusion

In this article, we find random characters from strings using multiple functions and Excel features. We also generate a custom function to find characters from strings using VBA Macro Code. Functions like FIND and SEARCH find characters with ease. Combined functions also show status in TRUE or FALSE of characters present in any strings. Hope you find these methods useful. Comment, if have further queries or have something to add.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo