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

Fortunately, if you are looking for some of the easiest ways to find substring using VBA, then you are in the right place. After going through this article, you will be able to find the position of a substring or extract data using this substring or change the format of a substring easily. In this article, I will show you 8 easy ways to find substring using VBA in Excel. Let’s get into the article.

Here, we have generated the FindSubstring function with the help of VBA to find substrings in Excel. Read through the rest of the part to learn more about the process along with some other methods.

8 Suitable Ways to Find Substring Using VBA in Excel

Here, I have the following data table by which I will show the ways of finding the substring in a string using VBA. I will also try to show the ways of finding substring in a random string. I have performed this task using Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Finding Substring Using InStr Function in VBA

If you want to find your desired substring in a string using VBA, you can use the InStr function in the VBA code. Hence, follow the steps below.

Steps:

• Go to Developer Tab>>Visual Basic Option.

• Then, the Visual Basic Editor will open up.
• Go to Insert Tab >> Module Option.

• After that, write the following code.

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

• Press F5.
• After that, you will get the following Message Box containing the position of the “think” substring.

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

If you want to find your desired substring in a string irrespective of the case using VBA, then follow this method. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type the following code.

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 case-insensitive substring.
• Press F5.
• Then, you will get the following Message Box containing the position of the “think” substring.

• Similarly, you can do the same by using 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 case-insensitive substring.

• Press F5.
• Afterwards, you will get the following Message Box containing the position of the “think” substring.

Method-3: Using InstrRev Function in VBA to Find Substring

Here, I will show the way to find a substring from the end of a string. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type 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
• Then, you will get the following Message Box containing the position of the second “I” substring from the right side.

Method-4: Finding Position of Substring in String in Range of Data

If you want to find the special character “@” in the Email Id, then follow this method. I have added here the Position column for this purpose. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type the following code and save the code.

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

• Select the output Cell E5.
• Type the following function (created by VBA).

• Here, D5 is the cell that contains the string.

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

• Afterwards, you will get the positions of the special character “@” in the Email Id.

Read More: How to Find String with VBA in Excel

Method-5: Checking Certain Substring in String in Range of Data with VBA

Suppose, you want to write Pass or Fail to correspond to the names of the students depending on the Result column where Pass or Fail has been written in a bracket. To find this substring in the Result column and write down it in the Pass or fail column follow this method. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type 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.
• Then, you will get the following outputs in the Pass or fail column.

Method-6: Checking Certain Substring in String and Extracting Data

I will show the way to find the students named Michael in the Student Name column and extract their corresponding data using VBA in this method. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type 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.
• After that, you will get the following extracted data for the students having the name Michael.

Method-7: Searching Substring for Word with VBA

If you want to find the substring as a word, then follow this method. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type 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

• Here, it will check out if the string contains is and then its position will be given.

• Press F5.
• Afterwards, you will get the following message box which shows the word found in position:6 (the position of is).

• However, you can test out this code for a word that is not in the string.
• Type 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.
• Afterwards, you will get the following message box which shows the word not found.

Method-8: Using Instr and LEFT Functions to Find Substring

Here, I will explain the way to find the position of a substring in a string and extract the texts before this substring by using VBA and the LEFT function. Hence, follow the steps below.

Steps:

• Follow the Steps of Method-1.
• Type 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 substrings before is.

• Press F5.
• Afterwards, you will get the following message box which shows Here (substring before is).

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Conclusion

In this article, I tried to cover the easiest ways to find substring using VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Further Readings

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