Excel VBA INSTRREV

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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more of MS Excel. Therefore, we can use them while writing any macro in Microsoft Visual Basic Editor.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA INSTRREV (wallstreetmojo.com)

Syntax

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

We can specify the following values for this argument.

Return Values

  • INSTRREV 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, the function returns to start.If a string match is found within a string check, the function returns the position at which the match is found.

How to Use VBA INSTRREV Function?

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 codeUse The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. 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 namesSplit NamesSplit Name in Excel refers to separating the names into two distinct columns. It splits the whole name into First Name, Last Name, and Middle Name. We can separate names using several ways such as the “Text to Column technique” and the “Formula technique.read more 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.

  • 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