Hello, Excel Avon visitor
String Functions
DOWNLOAD USED EXCEL FILE FROM HERE>>
So, today’s post is for How to use String Functions (Left, Right and Mid) in Excel VBA, as in the previous post, some functions of String function like lower case, upper case. We have told the use of trim function, len function and today we are going to tell you left. Use of Right and Mid Function. There are many string functions in VBA. They are all classified under string or text functions. Some important functions are LEFT function to get values from left side and RIGHT function to get values from right side or MID function.
To understand for String Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Left Function
We need to use the VBA LEFT function to remove the characters from the left side of the string. First, let’s look at the syntax of the LEFT function.
Left (string, length as Long)
String – which we are trying to extract.
Length – Length, how many characters you need from the left side of the supplied String.
To understand Left Function in Excel VBA, we will write a subroutine for Left Function in Excel VBA after inserting the module in VBE.
Sub UseLeft() End Sub
After writing the subroutine we will define the variable
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet End Sub
After we define the variable, we will use Set function for activesheet.
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet End Sub
We have defined i for long and will write i for starting range to LastRow
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 End Sub
Now I will use Loop for every next step
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 next i End Sub
Assign inptext the result of the left function output
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 inptext = ws.Range("A" & i). value next i End Sub
Add the result to the range in the worksheet
Sub UseLeft() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 inptext = ws.Range("A" & i). value Ws.Range("B" & i). Value = Left (inptext, 3) next i End Sub
Now I will click Run button
Now you can see the result inptext which is in A column, and we have extracted 3 characters from left
Right Function
Like how we have extracted values from the Left side of the string similarly, we can also extract from the right side of the string.
Right (string, length as Long)
String – which we are trying to extract.
Length – Length, how many characters you need from the Right side of the supplied String.
To understand Right Function in Excel VBA, we will write a subroutine for right Function in Excel VBA after inserting the module in VBE.
Sub UseRight() End Sub
After writing the subroutine we will define the variable
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet End Sub
After we define the variable, we will use Set function for activesheet.
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet End Sub
We have defined i for long and will write i for starting range to LastRow
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 End Sub
Now I will use Loop for every row
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 next i End Sub
Assign inptext the result of the left function output
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 inptext = ws.Range("D" & i). value next i End Sub
Add the result to the range in the worksheet
Sub UseRight() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet For i = 2 to 21 inptext = ws.Range("D" & i). value Ws.Range("E" & i). Value = Right (inptext, 3) next i End Sub
Now I will click Run button
Now you can see the result inptext which is in D column, and we have extracted 3 characters from Right.
Mid Function
We can extract the characters from the left and right sides and the middle of the string. Below is the syntax of the VBA MID function.
Mid (string, Start as Long, [Length])
String – which string do we need the middle value?
Start as Long – What is the starting position number to extract characters?
Length – How many characters need to be removed?
To understand Mid Function in Excel VBA, we will write a subroutine for Mid Function in Excel VBA after inserting the module in VBE.
Sub UseMid() End Sub
After writing the subroutine we will define the variable
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet End Sub
After we define the variable, we will use Set function for activesheet.
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet End Sub
We have defined i for long and will write i for starting range to LastRow
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet Lastrow = 21 For i = 2 to Lastrow End Sub
Now I will use Loop for every row
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet Lastrow = 21 For i = 2 to Lastrow next i End Sub
Assign inptext the result of the left function output
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet Lastrow = 21 For i = 2 to Lastrow inptext = ws.Range("G" & i). value next i End Sub
Add the result to the range in the worksheet
Sub UseMid() Dim inptxt as String Dim i as Long Dim Lastrow as long Dim WS as Worksheet Set WS = Activesheet Lastrow = 21 For i = 2 to Lastrow inptext = ws.Range("G" & i). value Ws.Range("M" & i). Value = Mid (inptext, 6, 3) next i End Sub
Now I will click Run button
Now you can see the result inptext which is in G column, for getting 3 characters from middle I will use middle function.