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.

**You can also see well-explained video here about String Function in Excel VBA**