How to Find Substring Using VBA in Excel (8 Easy Ways)

In this article, we will demonstrate how to use Excel VBA to find the position of a substring in a string, to extract data using this substring, and to change the format of a substring.

In the image above, we generated the FindSubstring function using VBA to find substrings in Excel.

We’ll use the following data table to demonstrate our methods. We used Microsoft Excel 365 version, but you can use any other version at your disposal.

Method 1 – Finding a Substring Using InStr Function

We can use the InStr function in the VBA code to find a substring in a string.

Steps:

• Go to Developer Tab >> Visual Basic Option.

The Visual Basic Editor will open up.

• Go to Insert Tab >> Module Option.

• Enter the following code in the module window:

Sub FindFirst()
Dim Pos As Integer
Pos = InStr(1, "I think therefore I am", "think")
MsgBox Pos
End Sub

• Press F5 to run the code.

The following Message Box containing the position of the substring is returned.

Method 2 – Finding a Case-Insensitive Substring in a String 

To find a substring in a string irrespective of the case using VBA:

Steps:

• Follow Method 1 to open a new module window
• Enter the following code in the window:

Public Sub caseinsensitive()
Dim Pos As Integer
Pos = InStr(1, "I Think Therefore I Am", "think",vbTextCompare)
MsgBox  Pos
End Sub

Here, vbTextCompare is used to find a case-insensitive substring.

• Press F5.

The following Message Box containing the position of the substring is returned.

• Alternatively, use the following code:

Option Compare Text
Public Sub caseinsensitive()
Dim Pos As Integer
Pos = InStr(1, "I Think Therefore I Am", "think")
MsgBox  Pos
End Sub

Here, Option Compare Text will find a case-insensitive substring.

• Press F5.

The following Message Box containing the position of the substring is returned.

Method 3 – Using InstrRev Function to Find a Substring

We can easily find a substring from the end of a string.

Steps:

• Follow Method 1 to open a new module window.
• Enter the following code:

Sub FindFromEnd()
MsgBox InStrRev("I think therefore I am", "I")
End Sub

Here, InStrRev will find the substring from the right side instead of the left side.

• Press F5.

The following Message Box containing the position of the second “I” substring from the right side is returned.

Method 4 – Finding the Position of a Substring in a Range of Data

Now we’ll find the special character “@” in the Email Id column. We added the Position column to our dataset for this purpose.

Steps:

• Follow the steps of Method 1 to open a new module.
• Enter the following code and save it:

Function FindSubstring(value As Range) As Integer
Dim Pos As Integer
Pos = InStr(1, value, "@")
FindSubstring = Pos
End Function

• in cell E5, enter the following function:

D5 is the cell that contains the string.

• Press ENTER.
• Drag down the Fill Handle tool.

The positions of the special character “@” in the Email Id in each row are revealed.

Read More: How to Find String with VBA in Excel

Method 5 – Checking for a Substring in a Range of Data

Suppose we want to write Pass or Fail in a new column called Pass or fail depending on the Result column, where Pass or Fail has been written in brackets.

Steps:

• Follow Method 1 to open a new module window.
• Enter the following code:

Sub CheckSubstring()
Dim cell As Range
For Each cell In Range("C5:C10")
If InStr(cell.value, "Pass") > 0 Then
cell.Offset(0, 1).value = "Passed"
Else
cell.Offset(0, 1).value = "Failed"
End If
Next cell
End Sub

• Press F5.

The following outputs in the Pass or fail column will be returned.

Method 6 – Checking for a Substring and Extracting Data

Now let’s find the students named Michael in the Student Name column and extract their corresponding data using VBA.

Steps:

• Follow Method 1 to open a new module window.
• Enter the following code:

Sub Extractdata()
Dim lastusedrow As Long
Dim i As Integer, icount As Integer
lastusedrow = ActiveSheet.Range("B100").End(xlUp).Row
For i = 1 To lastusedrow
If InStr(1, Range("B" & i), "Michael") > 0 Then
icount = icount + 1
Range("E" & icount & ":G" & icount) = Range("B" & i & ":D" & i).value
End If
Next i
End Sub

• Press F5.

The following extracted data for the students having the name Michael will be returned.

Method 7 – Searching a Substring for a Word

Steps:

• Follow Method 1 to open a new module window.
• Enter the following code:

Sub Stringforword()
Dim j As Integer
j = InStr("Here is what I am", "is")
If j = 0 Then
MsgBox "Word not found"
Else
MsgBox "Word found in position: " & j
End If
End Sub

The function will check if the string contains is and return its position.

• Press F5.

The following message box showing Word found in position: 6 (the position of is) is returned.

Let’s test this code with a word that is not in the string.

• Enter the following code:

Sub Stringforword()
Dim j As Integer
j = InStr("Here is what I am", "are")
If j = 0 Then
MsgBox "Word not found"
Else
MsgBox "Word found in position: " & j
End If
End Sub

• Press F5.

The following message box showing word not found is returned.

Method 8 – Using Instr and LEFT Functions to Find Substring

Now let’s find the position of a substring in a string and extract the text before this substring using VBA and the LEFT function.

Steps:

• Follow Method 1 to open a new module window.
• Enter the following code:

Sub InstrandLeft()
Dim txt As String
Dim j As Long
txt = "Here is what I am"
j = InStr(txt, "is")
MsgBox Left(txt, j - 1)
End Sub

 j = InStr(txt, "is") is the position of the substring is, and Left(txt, j - 1) will extract the substring before is.

• Press F5.

The following message box showing Here (the substring before is) is returned.

Download Practice Workbook

Further Reading

• How to Find String in a Cell Using VBA in Excel
• VBA Instr Case Insensitive