How to use String Functions (Left, Right and Mid) in Excel VBA Easily (3 functions)

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.

vba-string-function-in-excel-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

sring-function-CASE_in-Excel-VBA

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

left-function-in-vba

Now you can see the result inptext which is in A column, and we have extracted 3 characters from left

left-function-in-vba (2)

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

left-function-in-vba

Now you can see the result inptext which is in D column, and we have extracted 3 characters from Right.

right-function-in-vba.png

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?

LengthHow 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

left-function-in-vba

Now you can see the result inptext which is in G column, for getting 3 characters from middle I will use middle function.

String-functions-in-Excel-vba.png.png

So, I hope you have understood How to use String Functions (Left, Right and Mid) in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
You can also see well-explained video here about String Function in Excel VBA

Leave a Reply