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 func­tion returns the posi­tion of first occur­rence of a string into another string, as a vari­ant, based upon the argu­ments are provided.


InStr([start, ]main­String, searched­String[, compare])


  • start
    • Optional
    • Type:     Numeric
    • Start­ing posi­tion from where search has to be started.
    • Required if com­pare argu­ment is specified.

  • main­String
    • Manda­tory
    • Type: String
    • String being searched.
  • searched­String
    • Manda­tory
    • Type: String
    • String to be searched.
  • Com­pare
    • Optional
    • Spec­ify type of string comparison.
    • Set­tings


Con­stant Value Descrip­tion
vbBina­ryCompare 0 Per­forms a binary comparison.
vbTextCom­pare 1 Per­forms a tex­tual comparison.
vbData­baseC­om­pare 2 Microsoft Access only. Per­forms a com­par­i­son based on infor­ma­tion in your database.





Con­di­tion Result
main­String is zero-length 0
main­String is NULL NULL
searched­String is zero-length Start
searched­String is NULL NULL
searched­String is not found 0
searched­String found in mainString Posi­tion 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()                       |                         Instr­REV()                       |                        LCase()

UCase()                      |                         Left()                                |                        Right()

LTrim()                        |                         Mid()                                 |                        Trim()

RTrim()                       |                        Replace()                          |                        Space()

Len()                           |                        Str­Comp()                         |                        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: