str = “DataFlair”
\nHere, str is a variable that holds the string \u201cDataFlair\u201d.<\/p>\n
Examples of Strings in Excel<\/strong><\/p>\n 1. Alphabets:<\/strong><\/p>\n Str1 = “DataFlair”<\/p>\n 2. Numbers:<\/strong><\/p>\n Str2 = “17”<\/p>\n 3. Special characters<\/strong><\/p>\n Str3 = “!@#$”<\/p>\n 4. Different characters<\/strong><\/p>\n Str4 = “dataflair123@gmail.com\u201d<\/p>\n Other Syntax<\/strong><\/p>\n String(number, character)<\/p>\n Parameter Description<\/strong><\/p>\n 1. Number<\/strong> – It is an integer value that specifies the number of times the character should be repeated. It is one of the required parameters.<\/p>\n 2. Character<\/strong> – It is a character value that holds the character that has to be repeated for a specific number of times.<\/p>\n For Example<\/strong><\/p>\n When you save and run the code, the following output appears:<\/p>\n Two strings or values are combined together using a concatenate operator.<\/p>\n For example, “Data” & “Flair\u201d<\/p>\n Output: DataFlair<\/p>\n To use string operators, follow the below steps:<\/p>\n 1: Open an Excel workbook and save it as excel macro-enabled workbook (*.xlsm)<\/p>\n 2: Add a button using macros.<\/p>\n 3: Finally, press ok.<\/p>\n 4: Once the code window appears, type the following code.<\/p>\n For Example<\/strong>:<\/p>\n Explanation of the code:<\/strong><\/p>\n 5: Click on the save button or press ctrl+s.<\/p>\n 6: Close the vba code editor window.<\/p>\n Note: Check whether the design mode is on or off. If it is on, then the application wouldn\u2019t work.<\/strong><\/p>\n 7: Click on the Design Mode to turn it off and then click on the button for the output.<\/p>\n Your GUI appears as follows:<\/p>\n Once you click on the button, you will get the following result:<\/p>\n Various types of VBA String Manipulation functions are listed below:<\/p>\n Join function concatenates two or more strings using ampersand(&).<\/p>\n Example of VBA Join Function<\/strong><\/p>\n Output<\/strong><\/p>\n Note<\/strong>: To have a space between words, use ” “.<\/p>\n Left function extracts the leftmost characters from a string.<\/p>\n Example of Left Function in VBA<\/strong>:<\/p>\n Output<\/strong><\/p>\n Right function extracts the rightmost characters from a string.<\/p>\n Example of VBA Right Function<\/strong><\/p>\n Output<\/strong><\/p>\n Mid function extracts the mid characters of the string.<\/p>\n Example of VBA Mid Function<\/strong><\/p>\n Note<\/strong>: In \u201cMsgBox Mid(str, 11, 9)\u201d tells the function to extract 9 characters from the 11th position of the string. The third argument is optional, if we don\u2019t provide the third argument, it fetches the string until the end.<\/p>\n Output<\/strong>:<\/p>\n Len function finds the length of the string. While calculating the length, it also considers the space in the string.<\/p>\n Example of VBA Len Function<\/strong><\/p>\n Output<\/strong><\/p>\n <\/p>\n Note<\/strong>: There are totally 30 characters available in the \u2018str\u2019 string including the spaces between the words.<\/p>\n InStr function fetches the position of a substring in a string.<\/p>\n Example of VBA InStr Function<\/strong><\/p>\n Output<\/strong><\/p>\n Note<\/strong>: DataFlair position starts from the 11th index position in the string.<\/p>\n 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:<\/p>\n This string function starts its search from right to the left and this returns the first occurrence of one substring within the other string.<\/p>\n Example of VBA InstrRev Function<\/strong><\/p>\n Output<\/strong><\/p>\n This function converts the string to lowercase letters and returns the output.<\/p>\n Example of VBA LCase Function<\/strong><\/p>\n Output<\/strong><\/p>\n This function converts the string to uppercase letters and returns the output.<\/p>\n Example of VBA UCase Function<\/strong><\/p>\n Output<\/strong><\/p>\n 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.<\/p>\n Example of VBA ProperCase<\/strong><\/p>\n Output<\/strong><\/p>\n This function removes the blank spaces from the left side of the string.<\/p>\n Example of VBA LTrim Function<\/strong><\/p>\n Output<\/strong><\/p>\n This function removes the blank spaces from the right side of the string.<\/p>\n Example of VBA RTrim Function<\/strong><\/p>\n Output<\/strong><\/p>\n 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.<\/p>\n This function removes the blank spaces from the right and left side of the string.<\/p>\n Example of VBA Trim Function<\/strong><\/p>\n Output<\/strong><\/p>\n This VBA string helps to replace a specific part of a string by some other string or value for a specific number of times.<\/p>\n Example of VBA Replace Function<\/strong>:<\/p>\n Output<\/strong><\/p>\n Using the space function in vba, we can add the number of times the spaces in a string.<\/p>\n Example of VBA Space Function<\/strong>:<\/p>\n Output<\/strong><\/p>\n This function compares the two strings and returns any of the one value from -1,0,1.<\/p>\n Example of VBA String Compare Function<\/strong><\/p>\n Output<\/strong><\/p>\n This function reverses the specified string in VBA.<\/p>\n Example of VBA String Function<\/strong>:<\/p>\nSub str_operator()\r\nMsgBox (\"Line 1 :\" & String(5, \"A\"))\r\nEnd Sub<\/pre>\n
![\"str](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/06\/str-syntax-op.png\")
<\/a><\/p>\nString Operators in VBA<\/h3>\n
1. Concatenate (&)<\/h4>\n
How to use String Operators in Excel?<\/h4>\n
\n
<\/a><\/p>\nSub concat()\r\nMsgBox \"Data\" & \"Flair\", vbOKCancel, \"Concatenation using Operator\"\r\nEnd Sub<\/pre>\n
\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nVBA String Manipulation Functions<\/h3>\n
1. Join Function in VBA<\/h4>\n
Sub concat()\r\n Dim a As String, b As String, c As String, d As String\r\n a = \"This\"\r\n b = \"is\"\r\n c = \"DataFlair\"\r\n d = \"example!\"\r\n MsgBox a & \" \" & b & \" \" & c & \" \" & d\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n2. Left Function in VBA<\/h4>\n
Sub concat()\r\n Dim str As String\r\n str = \"DataFlair Tutorial\"\r\n MsgBox Left(str, 9)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n3. Right Function in VBA<\/h4>\n
Sub concat()\r\n Dim str As String\r\n str = \"DataFlair Tutorial\"\r\n MsgBox Right(str, 8)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n4. Mid Function in VBA<\/h4>\n
Sub concat()\r\n Dim str As String\r\n str = \"This is a DataFlair - Tutorial\"\r\n MsgBox Mid(str, 11, 9)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n5. Len Function in VBA<\/h4>\n
Sub concat()\r\n Dim str As String\r\n str = \"This is a DataFlair - Tutorial\"\r\n MsgBox Len(str)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n6. InStr Function in VBA<\/h4>\n
Sub concat()\r\n Dim str As String\r\n str = \"This is a DataFlair - Tutorial\"\r\n MsgBox InStr(str, \"DataFlair\")\r\nEnd Sub<\/pre>\n
<\/a><\/p>\nOther String Functions in VBA<\/h3>\n
1. InstrRev Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \u201cDataFlair\u201d\r\n MsgBox(\u201cThe substring appears in the position : \u201c& InstrRev(Var, \u201cl\u201d))\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n2. LCase Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \"DataFlair\"\r\n MsgBox (LCase(Var))\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n3. UCase Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \"DataFlair\"\r\n MsgBox (UCase(Var))\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n4. ProperCase Function in VBA<\/h4>\n
Sub Initialize()\r\nDim var1 As String\r\nDim var2 As String\r\nvar1 = \"DataFlair articles are new and interesting\"\r\nvar2 = StrConv(var1, vbProperCase)\r\nMsgBox (var2)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n5. LTrim Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \" DataFlair\"\r\n MsgBox (LTrim(Var))\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n6. Rtrim Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \" DataFlair \"\r\n MsgBox (RTrim(Var))\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n7. Trim Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \" DataFlair \"\r\n MsgBox (Trim(Var))\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n8. Replace Function in VBA<\/h4>\n
Sub str_operator()\r\n Var = \" The contents are available in the Google Website \"\r\n MsgBox (Replace(Var, \"Google\", \"DataFlair\"))\r\nEnd Sub\r\n<\/pre>\n
<\/a><\/p>\n9. Space Function in VBA<\/h4>\n
Sub str_operator()\r\n Var1 = \"DataFlair\"\r\n var2 = \"articles \"\r\n MsgBox (Var1 & Space(3) & var2)\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n10. String Compare Function in VBA<\/h4>\n
\n
Sub str_operator()\r\n Var1 = \"DataFlair\"\r\n Var2 = \"articles\"\r\n MsgBox (StrComp(Var1, Var2))\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n11. String Reverse Function in VBA<\/h4>\n
Sub str_operator()\r\n Var1 = \"DataFlair\"\r\n MsgBox (StrReverse(Var1))\r\nEnd Sub<\/pre>\n