VBA-Excel : Strings Functions – Instr

Description:

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

Format:

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

Arguments:

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.

INSTR

Results:

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 Macroing