How to Find Substring Using VBA (9 Ways)

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

Sample content

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.

VBA find substring

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

VBA find substring

Then, the Visual Basic Editor will open up.

➤Go to Insert Tab>> Module Option

VBA find substring

After that, a Module will be created.

finding substring in a string

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.

finding substring in a string

➤Press F5

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

finding substring in a string

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.

case-insensitive substring

➤Press F5

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

VBA find 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.

case-insensitive substring

➤Press F5

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

case-insensitive 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.

finding substring from right

➤Press F5

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

VBA find substring

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.

VBA find substring

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.

position of special substring in a string

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

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

position of special substring in a string

➤Press ENTER
➤Drag down the Fill Handle Tool

position of special substring in a string

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

VBA find substring


Similar Readings:


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.

VBA find substring

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.

checking a substring in string

➤Press F5

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

VBA find substring

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.

VBA find substring

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.

checking a substring and extracting data

➤Press F5

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

checking a substring and extracting data

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

searching substring for word

➤Press F5

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

VBA find substring

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

searching substring for word

➤Press F5

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

VBA find substring

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.

using InStr and LEFT function

➤Press F5

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

VBA find substring

Method-9: Bolding a Certain Substring in a String

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

Bold a substring

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.

Bold a substring

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

Bold a substring

Then, a Macro wizard will appear.

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

VBA find substring

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

Bold a substring

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.

practice

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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo