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

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

Method 1 – Using the FIND Function to Find a Character in Excel String

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.

Read More: How to Find Character in String from Right in Excel

Method 2 – Using SEARCH Function to Find a Character in Excel String

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.

Read More: How to Find from Right in Excel

3. Combining ISNUMBER and FIND Functions to Search for a a Character in a String

The ISNUMBER function returns TRUE or FALSE text depending on the numeric or non-numeric cell values. The syntax of the ISNUMBER function is:

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

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

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

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

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

Read More: Excel Find Last Occurrence of Character in String

Method 4 – Joining ISNUMBER and SEARCH Functions to Find a Character in Excel String

A combination of using ISNUMBER and SEARCH functions can bring up the character status as TRUE or FALSE as well.

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

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

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

Method 5 – Applying Find Feature to Search for a Character in Excel String

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

Another setting in the Find section is Default. You can change the settings depending on your search or data types.

Click on Find Next.

Step 3: cells with an “a” are marked with a Green Rectangular every time we click on Find Next. You can substitute the character “a” with any other character.

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

Method 6 – Using IF, ISNUMBER and FIND Functions to Find a Character

The syntax of the IF function is:

The IF function needs a logical_text to test a cell reference which then results in specific texts we set.  We can use a combination of the ISNUMBER and FIND functions 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 it shows “Not Found”.

Method 7 – Using IF, ISNUMBER, and SEARCH Functions to Find a Character in a String

We can use the SEARCH function instead of the FIND function because they are quite similar in their outcomes. In this case, the combination of the IF, ISNUMBER, and SEARCH functions create the same outcome as that in Method 6.

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

The formula is very similar to Method 6.

Step 2: Hit ENTER and then Drag the Fill Handle. The outcome shows either “Found” or “Not Found” depending on their logical_test.

Read More: How to Find * Character Not as Wildcard in Excel

Method 8 – Using Excel VBA Macro Code to Search for a Character

Using the VBA Macro Code we generate a custom function named FindM to find the occurrence of any character in a string.

Step 1: Press ALT+F11. The Microsoft Visual Basic window will open up. Select Insert > Choose Module.

Step 2: Paste the following Macro Code in the Module:

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 contains a custom function FindM which we use create the following syntax:

 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 and Drag the Fill Handle. All the character positions appear in the cells as shown in the image below.

Download Practice Workbook

Related Articles

• How to Find Text in Cell in Excel
• How to Check If Cell Contains Specific Text in Excel
• How to Find If Range of Cells Contains Specific Text in Excel

<< Go Back to Find in String | String Manipulation | Learn Excel