Now let’s find out what would happen if we wanted to get the position from a certain number. VBA to Find Text from a Specific Position in 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.Ģ.

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. 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. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm.

MsgBox InStr('Happiness is a choice', 'choice')

Now in the code window, write a simple InStr program inside a VBA Sub Procedure (see below).

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

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

VBA to Find Position of Text in Stringīelow is an example of InStr to find the position of a text in a string. Let’s see some simple examples to get the positions of certain strings in a given string using VBA.

vbDatabaseCompare -> performs a database comparison, return value 2īy default, InStr takes vbBinar圜ompare as the compare argument.Ĩ Easy Examples to Find Specific String Position in Given String Using VBA.

vbTextCompare -> performs a text comparison, return value 1.

vbBinar圜ompare -> performs a binary comparison, return value 0.

This argument can be the following values, But if you want to run a case insensitive InStr, then you can pass the argument here to perform a certain comparison.

The InStr function is case-sensitive by default. The string to search for in the primary string. So, you can leave this blank if you want to.

By default, the InStr function calculates the character position by counting from 1, not from the start position.

Where to find developer tab in excel