Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel : Strings Functions – Instr


Instr: This function returns the position of first occurrence of a string into another string, as a variant, based upon the arguments are provided.


InStr([start, ]mainString, searchedString[, compare])


  • start
    • Optional
    • Type:     Numeric
    • Starting position from where search has to be started.
    • Required if compare argument is specified.

  • mainString
    • Mandatory
    • Type: String
    • String being searched.
  • searchedString
    • Mandatory
    • Type: String
    • String to be searched.
  • Compare
    • Optional
    • Specify type of string comparison.
    • Settings


Constant Value Description
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.





Condition Result
mainString is zero-length 0
mainString is NULL NULL
searchedString is zero-length Start
searchedString is NULL NULL
searchedString is not found 0
searchedString found in mainString Position at which match is found
Start> searchedString 0
Function FnInstrOperations()

   Dim mainString

   Dim searchString

   mainString = "SumitJain"

   searchString = "i"

   MsgBox "Position of 'i' is " & InStr(1, mainString, searchString, vbTextCompare)

   ' Output : Position is 'i' is 4

   searchString = "i"

   MsgBox "Position of 'i' is " & InStr(1, mainString, searchString, vbBinaryCompare)

   ' Output : Position is 'i' is 4

   searchString = "i"

   MsgBox "Position of 'i' is " & InStr(mainString, searchString)

   ' Output : Position is 'i' is 4

End Function 

Also Read About  Other String() Functions

INSTR()                       |                         InstrREV()                       |                        LCase()

UCase()                      |                         Left()                                |                        Right()

LTrim()                        |                         Mid()                                 |                        Trim()

RTrim()                       |                        Replace()                          |                        Space()

Len()                           |                        StrComp()                         |                        String()

Happy Macro­ing :)

Sumit Jain

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: