VBA INSTRREV

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.

The INSTRREV function starts searching for the searchable string from the end, which we need to find out but counts the position from the beginning. Then, finally, one more INSTR VBA Function (stands for 'In String') 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 a searchable string.

INSTRREV and INSTR are built-in String/Text VBA function of MS Excel. Therefore, we can use them while writing any macro in Microsoft Visual Basic Editor.

As we can see in the above image, two mandatory and two optional arguments exist.

• StringCheck As String: This is the required argument. We need to give the string expression being searched.
• StringMatch As String: This argument is also required. We need to specify the string expression being searched for.
• Start As Long = -1: This is an optional argument. We specify the numeric expression. By default, it takes -1, meaning the search begins at the last character position. If we specify any positive value like 80, it starts searching from the end of the string in those 80 characters.
• Compare As VbCompareMethod = vbBinaryCompare As Long: This argument is optional.

We can specify the following values for this argument.

Return Values

1. INSTRREV function returns 0 if string check is of zero length or string match is not found or 'start' argument > length of string match.
2. This function returns 'Null' if a string check or string match is 'Null.'
3. If the string match is of zero length, the function returns to start.
4. If a string match is found within a string check, the function returns the position at which the match is found.

Suppose we have data for movie names and their directors. We want to split out director names.

We have data in 1201 rows. So if we do this task manually, it will take a lot of time.

To do the same, we will use the VBA code. The steps are:

• We need to click on the 'Visual Basic' Command available in the 'Code' Group in the '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 six variables—one for storing the values of the cells, which we will manipulate. Second for storing the position of the first space in the string, third for storing the last space in the string, fourth for storing the last row number, and fifth and sixth for row and column, which we will use to print the values in adjacent cells.

• We need to use the following code to find out the last used row in the sheet.

This code will first select cell B1 and then select the last used cell in the same column, and then we assign the cell's row number 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 the B column from row 2 to row 1201 one by one in the 's' variable to manipulate them.

• We need to set the value of the 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, there is no space in the string. Then, we want the string itself as output. For this, we will specify the condition using the 'If and Else statement' with an asterisk sign (denoting one or more characters) as follows:

• If there is space in the string, we want to split the string. To do the same, we have used the INSTR and INSTRREV function to find out the first and last space positions, respectively. It will help us find the first and last words in the string, respectively.

The INSTR function takes the argument as below:

Argument Details

• Start: From which position to start.
• String1: We need to give the string expression being searched.
• String2: We need to specify the string expression being searched for.

Compared as VbCompareMethod: Specifying Comparing method. By default, it is a binary comparison.

• We need to use the VBA 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 must use the RIGHT and LEN functions to extract the right characters from the string after the first space.

We must write Macro. Now, we need to run the Macro using the F5 key.

Code:

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.

This article has been a guide to VBA INSTRREV. Here, we discuss using the VBA INSTRREV function, examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: -

• ASC in VBA 
• Insert Text Box using VBA