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.
Introduction to InStr Function in Excel VBA
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.
|
| 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,
By default, InStr takes vbBinaryCompare as the compare argument. |
8 VBA Examples to Find String in a Given String in Excel
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
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.
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
- 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 second “choice” in the given string.
Read More: How to Find String in a Cell Using VBA in Excel
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”.
- 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
- 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,
it gives us the position (16) of the first text “choice”.
- But if we run the same code with the InStrRev Function then,
it gives us the position (27) of the last text “choice”.
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
- 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 SubYour code is now ready to run.
- 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
- 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
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.
Download Practice Workbook
You can download the free practice Excel template from here.
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.
