How to use String Function in Excel VBA Easily (5 Examples)

Welcome to Excel Avon

String Function in Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

So, reader today’s article String Functions is explained. The Excel VBA String Function is the most basic function used in VBA. String functions are so important. There are many string functions in VBA. They are all classified under string or text functions. Some important functions are LEN, TRIM and worksheet trim function to get the value from lower case to upper case or upper to lower case. And with the uses of all these, we will explain how we use it through an example.

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.

 CHANGE CASE STRING Function

To understand change case string in Excel VBA, we will write a subroutine for Change case in Excel VBA after inserting the module in VBE.

Sub ChangeCase()

End Sub

After writing the subroutine we will define the variable

Sub ChangeCase()
Dim inpTxt as String
Dim Result as String

End Sub

Now I will write value of ‘ inpTxt ‘.

Sub ChangeCase()
Dim inpTxt as String
Dim Result as String

inpTxt = "Sample Text"
End Sub

Now I will add comments for make lower case (all Letters small). and we will use the Lower-case function for the result.

Sub ChangeCase()
Dim inpTxt as String
Dim Result as String

inpTxt = "Sample Text"
'Make Lower case (all Letter small)

Result = VBA.LCase(inpTxt)
End Sub

Now we will use debug.Print to print the result

Sub ChangeCase()
Dim inpTxt as String
Dim Result as String

inpTxt = "Sample Text"
'Make Lower case (all Letter small)

Result = VBA.LCase(inpTxt)
debug.Print Result
End Sub

Now I will click Run button

After click Run button, we will go to the Immediate window for the result. As you can see the text is printed in lower case.

Upper Case String Function 

Now we will print the result in upper case, add comments for make Upper case (all Letters Capital). and we will use the Upper-case function for the result.

Sub ChangeCase() 
Dim inpTxt as String 
Dim Result as String 
inpTxt = "Sample Text" 

'Make Upper case (all Letter Capital)
Result = VBA.UCase(inpTxt)
End Sub

Now we will use debug. Print to print the result

Sub ChangeCase() 
Dim inpTxt as String 
Dim Result as String 
inpTxt = "Sample Text" 

'Make Upper case (all Letter Capital)
Result = VBA.UCase(inpTxt)

Debug.Print Result
End Sub

Now I will click Run button

After click Run button, we will go to the Immediate window for the result. As you can see the text is printed in Capital Letter.

LEN String function

The VBA LEN function returns the number of characters in a given string, i.e., to measure the length of the text or the number of characters in the text. Write Subroutine for length function.

Sub Use_Len()

End Sub

After writing the subroutine we will define the variable

Sub Use_Len()
Dim inpTxt as String

End Sub

Now I will add Comments for Len Function.

Sub Use_Len()
Dim inpTxt as String
'Len Function will get length of string
End Sub

Now I will write value of ‘ inpTxt ‘.

Sub Use_Len()
Dim inpTxt as String

'Len Function will get length of string
InpTxt = "This is Some Text"
End Sub

Now we will use debug.Print to print the length of Inptxt 

Sub ChangeCase()
Dim inpTxt as String

'Len Function will get length of string 
InpTxt = "This is Some Text"

Debug.Print Len (InpTxt)
End Sub

Now I will click Run button

After click Run button, we will go to the Immediate window for the result. As you can see the length of ‘This is Some Text’ printed.

Trim String Function

TRIM is a string function that removes spaces in Excel from the selected cell. For example, in space, we have three types: leading space, trailing space, and in-between space.

Write Subroutine for Trim function.

Sub Use_Trim()

End Sub

After writing the subroutine we will define the variable

Sub Use_Trim()
Dim inpTxt as String

End Sub

Now I will add Comments for Trim Function.

Sub Use_Trim()
Dim inpTxt as String
'Removes extra space from end and beginning of text

End Sub

Now I will write value of ‘ inpTxt ‘.

Sub Use_Trim()
Dim inpTxt as String

'Removes extra space from end and beginning of text
InpTxt = "     Sample Text  here   "
End Sub

Now we will use debug.Print to print the Trim of Inptxt.

Sub Use_Trim()
Dim inpTxt as String

'Removes extra space from end and beginning of text
InpTxt = "This is Some Text"

Debug.Print VBA.Trim(InpTxt)
End Sub

Now I will click Run button

After click Run button, we will go to the Immediate window for the result. As you can see the result of ‘Trim Function’ printed.

Worksheet Trim Function

Worksheet Trim function removes extra space from middle of text also.

Write Subroutine for Worksheet Trim function.

Sub Use_Trim()

End Sub

After writing the subroutine we will define the variable

Sub Use_Trim()
Dim inpTxt as String

End Sub

Now I will add Comments for Work Trim Function.

Sub Use_Trim()
Dim inpTxt as String
'Worksheet Trim function removes extra space from middle of text also

End Sub

Now I will write value of ‘ inpTxt ‘.

Sub Use_Trim()
Dim inpTxt as String

'Worksheet Trim function removes extra space from middle of text also
InpTxt = "     Sample Text  here   "
End Sub

Now we will use debug.Print to print the Trim of Inptxt.

Sub Use_Trim()
Dim inpTxt as String

'Worksheet Trim function removes extra space from middle of text also
InpTxt = "This is Some Text"

Debug.Print Application.WorksheetFunction.Trim(InpTxt)
End Sub

Now I will click Run button

After click Run button, we will go to the Immediate window for the result. As you can see the Result of ‘Worksheet Trim Function’ printed. In this example, you will also remove the space between the text, just give a space