How to Find String with VBA in Excel (8 Examples)

Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to find certain strings in another given string using VBA in Excel.


Download Practice Template

You can download the free practice Excel template from here.


InStr Function

Microsoft Excel has a built-in function called the InStr Function to find the position of specific strings in a given String.

Generic Syntax:

InStr([start], string1, string2, [compare])

Here,

Arguments Required/ Optional Definition
start Optional Starting position of the search.
  • By default, the InStr function calculates the character position by counting from 1, not from the start position. So, you can leave this blank if you want to.
string1 Required The string to search in, Primary String.
string2 Required The string to search for in the primary string.
compare Optional The InStr function is case-sensitive by default. But if you want to run a case insensitive InStr, then you can pass the argument here to perform a certain comparison. This argument can be the following values,
  • vbBinaryCompare -> performs a binary comparison, return value 0
  • vbTextCompare -> performs a text comparison, return value 1
  • vbDatabaseCompare -> performs a database comparison, return value 2

By default, InStr takes vbBinaryCompare as the compare argument.


8 Easy Examples to Find Specific String Position in Given String Using VBA

Let’s see some simple examples to get the positions of certain strings in a given string using VBA.

1. VBA to Find Position of Text in String

Below is an example of InStr to find the position of a text in a string.

  • Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • In the pop-up code window, from the menu bar, click Insert -> Module.

  • Now in the code window, write a simple InStr program inside a VBA Sub Procedure (see below).
Sub INSTR_Example()
MsgBox InStr("Happiness is a choice", "choice")
End Sub

vba find text in string

Your code is now ready to run.

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

run vba to find text in string

You will see that the pop-up message box will give you a number declaring the position of the text that you wanted to check.

Explanation:

Our primary string, “Happiness is a choice” is a 21 letter sentence (with spaces) and we wanted to find the position of the text “choice” in that string. The text “choice” started from the 16th position of the primary string, hence we got number 16 as our output in the message box.


2. VBA to Find Text from a Specific Position in String

Now let’s find out what would happen if we wanted to get the position from a certain number.

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, write a simple InStr program shown above and pass the value in the start argument according to the position you want to count your text from.
Sub INSTR_Example()
MsgBox InStr(17, "Happiness is a choice", "choice")
End Sub

vba to find text from specific position in string

  • Next, Run the code.

You will see that the pop-up message box will give you a number declaring the position of the text starting from a certain position that you wanted to check.

Explanation:

As we already knew (from the phase 1 discussion) that the text “choice” started from the position of 16, so we inserted two “choice” in the primary string and set 17 as our 1st parameter to skip the first “choice”. So, we Run the above macro and it showed us position number 27 which is exactly the position number of the secondchoice” in the given string.


3. VBA to Find Text with Case-insensitive InStr Function in String

From the introduction of the InStr function you already know that by default, the InStr function is case sensitive. Let’s find that out with an example.

See the following VBA code, where we wanted to find the position of the word “Choice” with a capital “C” in the string “Happiness is a choice” where choice is written with a small “c”.

vba to find case sensitive text in string

  • Run the code and find 0 as our output.

That’s because the InStr function treats capital “C” and small “c” differently. So it searched for the word “Choice” in the string and didn’t get any match, so returned 0.

  • To make the InStr function case-insensitive, set the compare argument to vbTextCompare (see below).
Sub INSTR_Example()
MsgBox InStr(1, "Happiness is a choice and choice", "Choice", vbTextCompare)
End Sub

vba to find case insensitive text in string

  • Run the code.

You will get the position of the text from the string, whether the text is written in capital letters or small letters.


4. VBA to Find Text from the Right of a String

Until now the InStr function was only giving us the position from the left side of the string. But what if you want to find the text position from the right side of the string.

The InStrRev Function searches from the right. The InStrRev function works very similarly to the InStr function and it will find you the position of a text from the right side of the string.

See the following examples to understand the difference.

  • If we run the following code with the InStr function then,

vba to find text from left in string

it gives us the position (16) of the first text “choice”.

  • But if we run the same code with the InStrRev Function then,

vba to find text from right in string

it gives us the position (27) of the last text “choice”.


Similar Readings:


5. VBA to Find Position of a Character in String

You can also find the position of a certain character in a string in the same way you found text.

  • Copy the following code into your VBA code window
Sub Find_Character()
Dim z As Long
z = InStr("Happiness is a choice", "e")
MsgBox z
End Sub

VBA to Find Position of a Character in String

  • And Run the macro.

The first “e” in our given string is at the number 7 position.


6. VBA to Find Substring in a String

Here we will learn how to find if a string contains a substring or not.

To get that, we have to run an IF Statement in our code.

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Public Sub FindSub()
If InStr("Happiness is a choice", "choice") = 0 Then
MsgBox "No match found"
Else
MsgBox "Match found"
End If
End Sub

Your code is now ready to run.

VBA to Find Substring in a String

  • Run the macro.

If your string contains the substring then you will get a match found, otherwise, it will return no match found. In our example, we wanted to find out whether our primary string “Happiness is a choice” contains the word “choice” or not. As it does, we get a Match found result.


7. VBA to Find String in a Cell Range

You can search for a certain text in a cell range of string and return a certain string.

Look at the following example where we will find “Dr.” and when there’s a match it will return “Doctor”.

  • Below is the code to get the result discussed above,
Sub Find_String_in_Range()
Dim cell As Range
For Each cell In Range("B5:B10")
If InStr(cell.Value, "Dr.") > 0 Then
cell.Offset(0, 1).Value = "Doctor"
End If
Next cell
End Sub

VBA to Find String in a Cell Range

  • Run the code and the result is shown below

  • You can modify the macro according to your need. For instance, if you want to find “Prof.” in any cell of string, and get “Professor” as return, then simply pass “Prof.” as the value instead of “Dr.” in the 4th line of the macro and “Professor” instead of “Doctor” in the 5th line of the macro and define the cell range number accordingly.

8. VBA to Find String in a Cell

You can also search for a certain text in a single cell of string and return a certain string.

  • Copy the following code and paste it into the code window.
Sub Find_String_in_Cell()
If InStr(Range("B5").Value, "Dr.") > 0 Then
Range("C5").Value = "Doctor"
End If
End Sub

VBA to Find String in a Cell

It will search for “Dr.” in Cell B5 and if it finds the match then returns “Doctor” in Cell C5.

  • You can modify the macro according to your need. For instance, if you want to find “Prof.” in any cell of string, and get “Professor” as return, then simply pass “Prof.” as the value instead of “Dr.” in the 2nd line of the macro and “Professor” instead of “Doctor” in the 3rd line of the macro and define the cell reference number accordingly.

Conclusion

This article showed you how to find certain texts in string in Excel using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo