VBA Trick of the Week :: MID() Function

MID() Function is generally used to extract a substring from a string. The syntax of the function is:

                          MID(string, Start, [Length])

Here, string is the string that is being manipulated, Start is the position from which substring is to be extracted and Length is the number of characters that are to be extracted. We usually see its use on the right hand side of the “=” sign as below:

                        strSubString = Mid(“Orange Pie”,1,6)

which assigns “Orange” to the variable strSubString. Suppose now I wish to substitute the substring “Orange” with “Banana”. Perhaps the most common way of doing it is by using Replace function. However, this can also be done using the MID function (this time on the left hand side of the “=” sign) as below:

                       strString = Replace(strString,”Orange”,”Banana”)

                       Mid(strString, 1, 6) = “Banana”