VBA String Functions | Operators for Manipulating Strings

String can contain alphabets, numbers and special characters in a sequence. The special advantage of a string is that it can hold all different characters at a single string. String operators help in manipulating the string data. One or more characters within a double quote is considered as a string.

Syntax

str = “DataFlair”
Here, str is a variable that holds the string “DataFlair”.

Examples of Strings in Excel

1. Alphabets:

Str1 = “DataFlair”

2. Numbers:

Str2 = “17”

3. Special characters

Str3 = “!@#$”

4. Different characters

Str4 = “[email protected]”

Other Syntax

String(number, character)

Parameter Description

1. Number – It is an integer value that specifies the number of times the character should be repeated. It is one of the required parameters.

2. Character – It is a character value that holds the character that has to be repeated for a specific number of times.

For Example

Sub str_operator()
MsgBox ("Line 1 :" & String(5, "A"))
End Sub

When you save and run the code, the following output appears:

String Operators in VBA

1. Concatenate (&)

Two strings or values are combined together using a concatenate operator.

For example, “Data” & “Flair”

Output: DataFlair

How to use String Operators in Excel?

To use string operators, follow the below steps:

1: Open an Excel workbook and save it as excel macro-enabled workbook (*.xlsm)

2: Add a button using macros.

• In order to create a macro, go to the developer tab and click on insert from the ribbon.
• Choose the button form from the drop down menu.
• Assign a macro by providing a name and click on the new button.

3: Finally, press ok.

4: Once the code window appears, type the following code.

For Example:

Sub concat()
MsgBox "Data" & "Flair", vbOKCancel, "Concatenation using Operator"
End Sub

Explanation of the code:

• Here, “Data”, “Flair” are two strings and the ampersand(&) in between them will concatenate and the obtained result will appear in the messagebox.
• VbOKCancel provides you two buttons such as ok and cancel.
• “Concatenation using Operator” will appear as the title of the msgbox.

5: Click on the save button or press ctrl+s.

6: Close the vba code editor window.

Note: Check whether the design mode is on or off. If it is on, then the application wouldn’t work.

7: Click on the Design Mode to turn it off and then click on the button for the output.

Your GUI appears as follows:

Once you click on the button, you will get the following result:

VBA String Manipulation Functions

Various types of VBA String Manipulation functions are listed below:

1. Join Function in VBA

Join function concatenates two or more strings using ampersand(&).

Example of VBA Join Function

Sub concat()
    Dim a As String, b As String, c As String, d As String
    a = "This"
    b = "is"
    c = "DataFlair"
    d = "example!"
    MsgBox a & " " & b & " " & c & " " & d
End Sub

Output

Note: To have a space between words, use ” “.

2. Left Function in VBA

Left function extracts the leftmost characters from a string.

Example of Left Function in VBA:

Sub concat()
  Dim str As String
  str = "DataFlair Tutorial"
  MsgBox Left(str, 9)
End Sub

Output

3. Right Function in VBA

Right function extracts the rightmost characters from a string.

Example of VBA Right Function

Sub concat()
  Dim str As String
  str = "DataFlair Tutorial"
  MsgBox Right(str, 8)
End Sub

Output

4. Mid Function in VBA

Mid function extracts the mid characters of the string.

Example of VBA Mid Function

Sub concat()
  Dim str As String
  str = "This is a DataFlair - Tutorial"
  MsgBox Mid(str, 11, 9)
End Sub

Note: In “MsgBox Mid(str, 11, 9)” tells the function to extract 9 characters from the 11th position of the string. The third argument is optional, if we don’t provide the third argument, it fetches the string until the end.

Output:

5. Len Function in VBA

Len function finds the length of the string. While calculating the length, it also considers the space in the string.

Example of VBA Len Function

Sub concat()
  Dim str As String
  str = "This is a DataFlair - Tutorial"
  MsgBox Len(str)
End Sub

Output

Note: There are totally 30 characters available in the ‘str’ string including the spaces between the words.

6. InStr Function in VBA

InStr function fetches the position of a substring in a string.

Example of VBA InStr Function

Sub concat()
  Dim str As String
  str = "This is a DataFlair - Tutorial"
  MsgBox InStr(str, "DataFlair")
End Sub

Output

Note: DataFlair position starts from the 11th index position in the string.

Other String Functions in VBA

There are also other VBA string functions that help to work with the strings more efficiently and effectively. Some of the other VBA string functions are:

1. InstrRev Function in VBA

This string function starts its search from right to the left and this returns the first occurrence of one substring within the other string.

Example of VBA InstrRev Function

Sub str_operator()
  Var = “DataFlair”
  MsgBox(“The substring appears in the position : “& InstrRev(Var, “l”))
End Sub

Output

2. LCase Function in VBA

This function converts the string to lowercase letters and returns the output.

Example of VBA LCase Function

Sub str_operator()
   Var = "DataFlair"
   MsgBox (LCase(Var))
End Sub

Output

3. UCase Function in VBA

This function converts the string to uppercase letters and returns the output.

Example of VBA UCase Function

Sub str_operator()
   Var = "DataFlair"
   MsgBox (UCase(Var))
End Sub

Output

4. ProperCase Function in VBA

In VBA, we can convert the string to proper case which means the first letter of the string will be in capital and the other letters in the lower case.

Example of VBA ProperCase

Sub Initialize()
Dim var1 As String
Dim var2 As String
var1 = "DataFlair articles are new and interesting"
var2 = StrConv(var1, vbProperCase)
MsgBox (var2)
End Sub

Output

5. LTrim Function in VBA

This function removes the blank spaces from the left side of the string.

Example of VBA LTrim Function

Sub str_operator()
   Var = "                DataFlair"
   MsgBox (LTrim(Var))
End Sub

Output

6. Rtrim Function in VBA

This function removes the blank spaces from the right side of the string.

Example of VBA RTrim Function

Sub str_operator()
   Var = "                DataFlair                 "
   MsgBox (RTrim(Var))
End Sub

Output

Hope you noticed that only the right side of the blank space is removed and not the blank spaces in the left side of the string.

7. Trim Function in VBA

This function removes the blank spaces from the right and left side of the string.

Example of VBA Trim Function

Sub str_operator()
   Var = "                DataFlair               "
   MsgBox (Trim(Var))
End Sub

Output

8. Replace Function in VBA

This VBA string helps to replace a specific part of a string by some other string or value for a specific number of times.

Example of VBA Replace Function:

Sub str_operator()
   Var = " The contents are available in the Google Website "
   MsgBox (Replace(Var, "Google", "DataFlair"))
End Sub

Output

9. Space Function in VBA

Using the space function in vba, we can add the number of times the spaces in a string.

Example of VBA Space Function:

Sub str_operator()
   Var1 = "DataFlair"
   var2 = "articles "
   MsgBox (Var1 & Space(3) & var2)
End Sub

Output

10. String Compare Function in VBA

This function compares the two strings and returns any of the one value from -1,0,1.

• -1 denotes that string1 is less than string2.
• 0 denotes that string1 is equal to string2.
• 1 denotes that string1 is greater than string2.

Example of VBA String Compare Function

Sub str_operator()
   Var1 = "DataFlair"
   Var2 = "articles"
   MsgBox (StrComp(Var1, Var2))
End Sub

Output

11. String Reverse Function in VBA

This function reverses the specified string in VBA.

Example of VBA String Function:

Sub str_operator()
   Var1 = "DataFlair"
   MsgBox (StrReverse(Var1))
End Sub

Output

12. Split Function in VBA

The user can also split the string using the comma position in a cell value.

The worksheet holds the full name with a separation of a comma in the cell itself. Now, we are going to split the string into the left name and middle name. To split the string, execute the following code in your VBE editor.

Example of VBA Split Function

Sub Split()
Dim name As String, commapos As Integer, i As Integer
For i = 2 To 3
    name = Cells(i, 1).Value
    commapos = InStr(name, ",")
Cells(i, 2).Value = Left(name, commapos - 1)
Cells(i, 3).Value = Mid(name, commapos + 2)
Next i
End Sub

Output

Summary

1. VBA strings can hold different data types in a single variable.
2. It can execute string manipulation functions such as left, right, join etc.
3. Using the string manipulation function, we can also fetch the string length and a position of the substring.