VBA String Manipulation Functions & Operators

VBA String Operators

String data is used to hold data that is made up of numbers, characters, and symbols. “Jul-2015” is an example of a string data. It is made up of

  • Characters (Jul)
  • Symbol (-)
  • Numbers (2015)

String operators are used to manipulate string data. For example, you can concatenate the value of July-2015 from the first 3 letters of the month and the year like “Jul-2015”.

The following table shows the concatenation VBA string operator.

S/N Operator Description Example Output
1 & Concatenate: This operator is used to concatenate strings together “John ” & “Doe” John Doe

Example Source Code

VBA Operators

MsgBox “John ” & “Doe”, vbOKOnly, “Concatenate Operator”

Executing the above code produces the following result

VBA Operators

VBA String Manipulation Functions

VBA String Manipulation is an important element in Excel VBA for Macro programming and string operations. Excel provides various types of VBA String Manipulation functions like:

  • Join: is used to concatenate or join two strings
  • Left: is used to extract characters from the left side of the string
  • Right: is used to extract characters from the right side of the string
  • Mid: is used to extract a substring from the middle of a string
  • Len: is used to find the length of a string
  • Instr: is used to find the position of a substring in a string

Download the above Excel Code