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.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
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
Note:
Here, InStr(1, “I think therefore I am”, “think”) will return the position of the substring of a string. 1 is the start position, “I think therefore I am” is the string where you will find your desired substring, and “think” is the substring you want to find. It is by default case-sensitive so beware of the case of the substring you want to search.
- 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
Note:
It will create a function named FindSubstring (you can use any other name)
value is the cell reference that contains the string and it is declared as Range.
- Select the output Cell E5.
- Type the following function (created by VBA).
=FindSubstring(D5)
- 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.
Similar Readings:
- How to Find String in a Cell Using VBA in Excel (2 Methods)
- FindNext Using VBA in Excel (2 Examples)
- How to Find String with VBA in Excel (8 Examples)
- VBA Find Last Row in Excel (5 ways)
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
Note:
Here, the cell range is C5:C10 which is the Result column. InStr(cell.value, “Pass”) > 0 is the condition where the number is greater than zero (when the cell contains “Pass”) then the following line will continue and give the output in the adjacent cell as Passed. If the condition becomes false means a cell doesn’t contain any “Pass” then the line under Else will execute and give the output value in the adjacent cell as Failed. This loop will continue for each cell.
- 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
Note:
Here, I have used B100 as the Active Sheet Range but you can use any range according to your use. InStr(1, Range("B" & i), "Michael") > 0
is the condition for checking if the cell in column B contains Michael. Here, Range("E" & icount & ":G" & icount)
is the range where you want your output data and Range("B" & i & ":D" & i).value
will give the values from columns B to D.
- 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 andLeft(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).
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.