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

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

In the formula,

C4; is the find_text.

B4: is the within_text.

We use start_num position as Default.

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

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

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

In the formula,

C4; is the find_text.

B4: is the within_text.

We use start_num position as Default.

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

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

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

Here,

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

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.

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

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

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.

Similar Readings:

• Find If Cell Contains Specific Text in Excel
• How to Find If A Range of Cells Contains Specific Text in Excel (4 Methods)
• Find Value In Range in Excel (3 Methods)
• How to Find Text in Cell in Excel

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

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.

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.

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

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

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.

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”.

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

The formula contains all the arguments similar to Method 6.

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

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.

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

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

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

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.

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

• Excel Find Last Column With Data (4 Quick Ways)
• Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)
• How to Find Last Cell with Value in Column in Excel
• Find First Occurrence of a Value in a Range in Excel(3 Ways)