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. Let’s get into the article.

Download Workbook

9 Ways to Find Substring Using VBA 

Here, I have the following data table by which I will show the ways of finding 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 in a String Using VBA

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

Step-01:
➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.

➤Go to Insert Tab>> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

Sub FindFirst()

Dim Pos As Integer

Pos = InStr(1, "I think therefore I am", "think")

MsgBox Pos

End Sub

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 your substring you want to search.

➤Press F5

Result:
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.

Step-01:
➤Follow Step-01 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

Result:
Then, you will get the following Message Box containing the position of the “think” substring.

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

Result:
Afterward, you will get the following Message Box containing the position of the “think” substring.

Method-3: Using InstrRev function in VBA

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

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub FindFromEnd()

MsgBox InStrRev("I think therefore I am", "I")

End Sub

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

➤Press F5

Result:
Then, you will get the following Message Box containing the position of the second “I” substring from the right side.

Method-4: Finding the position of Substring in a String in a 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.

Step-01:
➤Follow Step-01 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

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.

Step-02:
➤Select the output Cell E5
➤Type the following function (created by VBA)

=FindSubstring(D5)
D5 is the cell that contains the string.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Afterward, you will get the positions of the special character “@” in the Email Id.

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

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.

Step-01:
➤Follow Step-01 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

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

Result:
Then, you will get the following outputs in the Pass or fail column.

Method-6: Checking a Certain Substring in a 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.

Step-01:
➤Follow Step-01 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

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 

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

Result:
After that, you will get the following extracted data for the students having the name Michael.

Method-7: Searching Substring for Word

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

Step-01:
➤Follow Step-01 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

It will check out if the string contains is and then its position will be given

➤Press F5

Result:
Afterward, you will get the following message box which shows the word found in position:6 (the position of is).

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

Result:
Afterward, you will get the following message box which shows the word not found.

Method-8: Using Instr and LEFT function

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.

Step-01:
➤Follow Step-01 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

Result:
Afterward, you will get the following message box which shows Here (substring before is).

Method-9: Bolding a Certain Substring in a String

You can bold the grades before brackets in the Result column by following this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code and save the code.

Sub Boldingsubstring()

Dim Cell As Range

Dim txt As Integer

For Each Cell In Selection

txtCount = Len(Cell)

txt = InStr(1, Cell, "(")

Cell.Characters(1, txt - 1).Font.Bold = True

Next Cell

End Sub

txt = InStr(1, Cell, "(") will return the position of the first bracket and Cell.Characters(1, txt - 1).Font.Bold will make the substring before the first bracket Bold.

Step-02:
➤Select the Result column
➤Go to Developer Tab>>Macros Option

Then, a Macro wizard will appear.

➤Select Boldingsubstring (the VBA code name) and then Run.

Result:
After that, the grades in the Result Column will be bolded.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

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.

The post How to Find Substring Using VBA (9 Ways) appeared first on ExcelDemy.