Syntax:<\/h3>\n
This is how Syntax will look like as soon as you type VBA.Instr <\/strong> in VB Editor<\/p>\n This is the start position where you want to start the search of your sub-string to find its first occurrence in the main string. This is an optional argument. If omitted search starts from the first character of the main string.<\/p>\n This is the main string in which the sub-string is to be searched. This is a mandatory argument.<\/p>\n This is the sub-string which has to be searched in the main string to find its first occurrence. This is again a mandatory argument.<\/p>\n This is a Numeric value ( 0 or 1) to indicate which method to use for comparison. 0<\/strong> is for performing a binary comparison. 1<\/strong> is for performing a textual comparison. The default method is 0 – vbBinaryCompare<\/strong><\/p>\n With the above theory you may not be more comfortable until you practice with the some example by running the VBA code. I have taken few possible cases with different parameters in the below examples. They will make you more comfortable in using this function \ud83d\ude42<\/p>\n In the below Example, I have omitted all the optional parameter and passed the String1 and String2<\/strong> which are the mandatory<\/strong> parameters in this function.<\/p>\n String1 : <\/strong> My Name is Vishwa Mitra vishwa mishra<\/i> In the above code all the optional parameters are omitted. Hence using all the default values for other parameters, this function has returned the first occurrence of sub-string vishwa<\/strong> in the main string. This function has returned the count as 12. It is very simple. As the name suggests in binary comparison, system compares the Numeric unicode value of each character. But in Text Comparison, comparison is based on the reference of the current world definition. SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 1)<\/i> SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 0)<\/i> With the above example difference between Binary Comparison and Text Comparison is clear. Though, rest all the parameters are same in both the statement but still result is different because comparison method is different. Reason is that in Binary Comparison Vishwa<\/strong> is not equal to vishwa<\/strong> but in text comparison they are equal.\n<\/p><\/blockquote>\n In the below example I will show you how Start number has an impact on the integer returned by this function.<\/p>\n In the above code first statement returns the integer as 1<\/strong> while the second statement returns as 8><\/strong> as shown in the below picture: Case 1 : <\/strong>In the first statement the Start number is set as 1<\/strong> hence Instr()<\/strong> function starts the search of sub-string vishwa<\/strong> right from the first letter of the main string vishwa vishwa Mitra mishra<\/strong> and it finds the first occurrence at the first place itself hence it returned the integer as 1<\/strong> Following are special cases in this function:<\/p>\n Zero (0) is returned by Instr(), if the Sub-String is not found in the main string from the start<\/strong> number for search provided in the parameter<\/i>. In the given example though the string vishwa<\/strong> is found but not starting from the 5th letter (Start=5) hence this function will consider as Sub-String NOT found in the main string and will return Zero (0)<\/strong> as shown in the below picture.<\/p>\n Zero (0) is returned by Instr() function, if main string is passed of Zero length. Refer the below picture<\/p>\n In this case this function returns the Start Number<\/strong> passed. Refer the below picture:<\/p>\n In this case when Start<\/i> number is greater than the length of the main string AND sub-string is NOT of Zero length, Zero is returned. Refer the below picture.<\/p>\n Case 1. <\/strong> It returns Zero because vishwa<\/strong> is NOT found in the string 5th letter (because Start = 5) Do comment of mail me. I try my best to clarify.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":" Instr() Function This is a VBA function and not a Worksheet function (Excel formula). This function returns the Position of First Occurrence of a SubString in a String. SubString could be a letter, Word or Sentence etc. It means this function always returns an Integer value. As mentioned in the definition of this function, it […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246],"tags":[],"class_list":["post-12188","post","type-post","status-publish","format-standard","hentry","category-macro"],"yoast_head":"\n![\"Instr](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Instr-Syntax.png\" "\\"Instr")
<\/p>\n\nVBA.Instr(<Start>, <String1>, <String2>, <CompareMethod>)<\/h3>\n
Parameters:<\/h2>\n
Start(Optional) :
\n<\/h3>\nString1(Mandatory) :
\n<\/h3>\nString2(Mandatory) :<\/h3>\n
CompareMethod (Optional) :<\/h3>\n
Examples:<\/h3>\n
Example 1 : Omitting all the Optional Parameters<\/h2>\n
\nString2 : <\/strong> vishwa<\/i><\/p>\n
\nVBA.Instr function will look like below with the above 2 argument passed in it:
\n
\nVBA.Instr(“My Name is Vishwa Mitra vishwa mishra”, “vishwa”)<\/strong>
\nFollowing picture shows the integer value which is returned by the above VBA statement (refer the below picture)
\n
\n![\"Instr-Example-1\"](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Instr-Example-1.png\" "\\"Instr-Example-1\\"")
<\/p>\nExplanation:<\/h3>\n
\nIn the above example default Start<\/i> number is passed as 1 and compare method is passed as 0 – BinaryMethod.<\/p>\nExample 2 : What is difference between Binary and Text comparison?<\/h2>\n
\nFor example: <\/strong> Upper case – V<\/strong> and Lower case – v<\/strong>. Both “V” and “v” has different unicode value but as a text both are same. This is the reason Below statement 1 returns the first occurrence as 1 while the statement 2 returns as 8 while both the statements are exactly same except the comparison method. Refer the below two examples:<\/p>\nStatement 1: Text Comparison<\/h3>\n
\n![\"Instr](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Inst-Statement-1-Text-Compare.png\" "\\"Instr")
<\/p>\nStatement 2: Binary Comparison<\/h3>\n
\n![\"Instr](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Inst-Statement-1-Binary-Compare.png\" "\\"Instr")
<\/p>\nExample 3 : Impact of Start number<\/h2>\n
\nFunction INSTR_EXAMPLE()\nDim myString As String\nDim SubStringFoundAt1 As Integer\nDim SubStringFoundAt2 As Integer\n SubStringFoundAt1 = VBA.InStr(1, \"vishwa vishwa Mitra mishra\", \"vishwa\", 0)\n SubStringFoundAt2 = VBA.InStr(2, \"vishwa vishwa Mitra mishra\", \"vishwa\", 0)\nEnd Function\n<\/code><\/pre>\n
\n![\"Instr](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Instr-Example-Start.png\" "\\"Instr")
<\/p>\nExplanation:<\/h3>\n
\n Case 2 : <\/strong>In the second statement start number is passed as 2<\/strong>; it means the Instr() VBA function will start the search from the second letter of the string vishwa vishwa Mitra mishra<\/strong>. This is the reason in this case this function will find the first occurrence of sub-string vishwa<\/strong> at the 8th position and it returned the integer as 8<\/strong><\/p>\nSpecial Cases in INSTR()Function<\/h3>\n
Case 1 : Sub-String NOT Found<\/h2>\n
Case 2 : Main String passed with Zero length<\/h2>\n
Case 3 : Sub-String passed with Zero length<\/h2>\n
Case 4 : Start<\/i> > length of Main string AND Sub-String is NOT of Zero Length<\/h2>\n
\nFunction INSTR_EXAMPLE()\nDim myString As String\nDim Case1 As Integer\nDim Case2 As Integer\nDim Case3 As Integer\n Case1 = VBA.InStr(5, \"vishwa Mitra mishra\", \"vishwa\", 0)\n Case2 = VBA.InStr(1, \"\", \"vishwa\", 0)\n Case3 = VBA.InStr(10, \"vishwa Mitra mishra\", \"\", 0)\n Case4 = VBA.InStr(100, \"vishwa Mitra mishra\", \"vishwa\", 0)\nEnd Function\n<\/code><\/pre>\n![\"Instr](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/04\/Instr-Special-Cases.png\" "\\"Instr")
<\/p>\nExplanation:<\/h2>\n
\nCase 2. <\/strong> Returns Zero because the length of the main string is Zero.
\nCase 3. <\/strong> In this case it returns the Start<\/strong> number passed in the function because the sub-string to be searched is of zero length.
\nCase 4. <\/strong> This returns Zero because the Start (100) is greater than the length of the main string (19) AND String2 is not of Zero length.<\/p>\nHave you got any question or doubt related to INSTR() Function? <\/h3>\n