Excel VBA INSTRREV
The MS Excel VBA INSTRREV Function, as stands for ‘In String Reverse’, returns the position of the first occurrence of a search string (substring) in another string, starting from the end of the string (from right to left) from which we are looking for a searchable string. There is one more function INSTR Function (stands for ‘In String’) that also searches for a string in another string and returns the position but this function starts the search from the beginning of the string from which we look for searchable string.
INSTRREV and INSTR, both are built-in String/Text VBA function of MS Excel. We can use them while writing any macro in Microsoft Visual Basic Editor.
Syntax of the VBA INSTRREV Function
As we can see in the above image, there are 2 mandatory and 2 optional arguments for the Excel VBA ‘InStrRev’ function.
- StringCheck As String: This is the required argument. We need to give string expression being searched.
- StringMatch As String: This argument is also required. We need to specify string expression being searched for.
- Start As Long = -1: This is an optional argument. We specify the numeric expression. By default, it takes -1 which means that the search begins at the last character position. If we specify any positive value like 80 then it starts searching from the end of the string in those 80 characters of left.
- Compare As VbCompareMethod = vbBinaryCompare As Long: This argument is optional.
We can specify the following values for this argument.
- INSTRREV VBA function returns 0 if string check is of zero length or string match is not found or ‘start’ argument > length of string match.
- This function returns ‘Null’ if a string check or string match is ‘Null’.
- If the string match is of zero length, then the function returns to start.
- If a string match is found within a string check, then the function returns the position at which match is found.
Let us understand the function using some examples.
Example of Excel VBA INSTRREV Function
Suppose, we have data for movie names and their directors. We want to split out director names.
4.6 (247 ratings)
We have data in 1201 rows. If we do this task manually, it is going to take a lot of time.
To do the same, we will use the VBA code. Steps are:
- We need Visual Basic Editor to write the code. This command can be found in ‘Developer’ If you are not able to find this tab, you can get this tab shown by using ‘Option’ command in File Menu.
- When we click, we get the ‘Excel Option’ dialog box, we need to choose ‘Customize Ribbon‘. Here we can find out the checkbox for the ‘Developer’ tab, we need to check the box and click on ‘OK’.
- We need to click on ‘Visual Basic’ Command available in ‘Code’ Group in ‘Developer’ Tab or we can press Alt+F11 to open the visual basic editor.
- We will insert a module using the ‘Insert’ menu.
- We will create a subroutine named ‘SplittingNames’.
- We need 6 variables. One for storing the values of the cells, which we will manipulate. Second for storing the position of first space in the string, third for storing the position of last space in the string, fourth for storing last row number, fifth and sixth for row and column which we will use to print the values in adjacent cells.
- To find out the last used row in the sheet, we need to use the following code.
This code will first select the cell B1 and then select the last used cell in the same column and then we assign the row number of the cell to the ‘LastRow’ variable.
- Now to manipulate all the cells in the B column, we will run a ‘for’ loop.
- We will store the value of cells of B column from row 2 to row 1201 one by one in ‘s’ variable to manipulate them.
- We need to set the value of variable ‘Column’ to 3 as we need to write the split names in C (3rd Column) and a column onward.
- If the string is only one word that means there is no space in string then we want the string itself as output. For this, we will specify the condition using ‘If and Else statement‘ with an asterisk sign (denoting one or more characters) as follows:
- If there is space in the string then we want to split the string. To do the same we have used VBA INSTR and INSTRREV function both to find out the first space position and last space position respectively. It will help us to find the first word and last word in the string respectively.
INSTR Function takes the argument as below:
- Start: From which position to start.
- String1: We need to give string expression being searched.
- String2: We need to specify string expression being searched for.
Compared as VbCompareMethod: Specifying Comparing method. By default, it is binary compare.
- We need to use the LEFT function to extract left characters from the string. We have used ‘Last Space-1’ to get the left characters before the last space.
We need to use the RIGHT and LEN functions to extract the right characters from the string after the first space.
Macro is written. Now we just need to run the macro using the F5 key.
Sub SplittingNames() Dim s As String Dim FirstSpace As Long Dim LastSPace As Long Dim LastRow As Long Dim Row As Long Dim Column As Long Sheet1.Range("B1").Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For Row = 2 To LastRow s = Sheet1.Cells(Row, 2).Value Column = 3 If s Like "* *" Then FirstSpace = InStr(1, s, " ") LastSPace = InStrRev(s, " ") Sheet1.Cells(Row, Column).Value = Left(s, LastSPace - 1) Sheet1.Cells(Row, Column + 1).Value = Right(s, Len(s) - FirstSpace) Else Sheet1.Cells(Row, Column).Value = s End If Next End Sub
We have a result now.
Things to Remember
INSTRREV function in excel VBA starts searching for the searchable string from the end of the string in which we need to find out but counts the position from the beginning.
This has been a guide to VBA INSTRREV. Here we discuss how to use INSTRREV Function in VBA along with excel examples and downloadable templates. Below are some useful excel articles related to VBA –