VBA String Functions | Operators for Manipulating Strings
Placement-ready Online Courses: Your Passport to Excellence - Start Now
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.
If you are Happy with DataFlair, do not forget to make us happy with your positive feedback on Google