VBA String functions in Excel

In this topic, The various functions used to manipulate and work with strings are discussed.

The string functions are also most often used functions irrespective of size and complexity of the program.

For instance, if a full name of a person from a sheet is required, then the first name and the last name has to be combined and returned as a single name. This is the most basic usage of string function, called "concatenation".

There are also other string functions such as truncation of characters on either side, conversion of numbers to strings and so on. These functions are discussed as below

1.& Operation

This is string concatenation wherein two or more strings are combined to form a single string.


This function is used to convert other data types, mostly numbers, to string data type.

3.Left() and Right()

Left(string,no_of charecters)

Right(string,no_of charecters)

Theses functions return a certain number of charecters from the string from either side as specified, upto the number of characters specified.

4.Ucase() and Lcase()

These functions are used to convert a given string to upper or lower case.


This function is used to find if a specific string is found in some other string.

The following example illustrates these functions

  1. Sub str_func()
  2. Dim str As String
  3. str = "hello"
  4. MsgBox str & " in upper case: " & UCase(str)
  5. MsgBox UCase(str) & " in lower case: " & LCase(str)
  6. MsgBox "Concatenation of " & UCase(str) & " Excel: " & UCase(str) & " Excel"
  7. MsgBox "First 3 charecters from " & str & ": " & Left(str, 3)
  8. MsgBox "Last 3 charecters from " & str & ": " & Right(str, 3)
  9. MsgBox "If hel is present in " & str & "?" & vbNewLine & _
  10. InStr(str, "hel")
  11. End Sub

The screenshot of VBA editor

excel vba string functions


excel vba string functions

excel vba string functions

excel vba string functions

excel vba string functions

excel vba string functions

excel vba string functions


You can find similar Excel Questions and Answer hereunder

1) How to convert a number or string into a data with the cdate function

2) Split string into multiple columns in Excel

3) How to format variable with VBA with the very useful format function. Define the number of decimal, the way a date is formated

4) Here we show you advanced VBA array functions, like splitting arrays, Lbound, Ubound

5) What are the various functions of the worksheet in Excel VBA

6) Find and count instances of a character in a string in Excel

7) How to use logical operators in VBA

8) String split in VBA in Excel

9) What are the date and time function in Excel VBA

10) How do i put double quotes in a string in VBA in Excel


Here the previous and next chapter