Strings in VBA
In this chapter, you'll find the most important VBA functions to
- manipulate strings such as concatenation, add or remove extra spaces or replace strings or part of strings and StrReverse;
- get substrings: find part of strings on the left or right side or in the mid.
- search a specified substring with InStr or InStrRev.
For a selection of other String function available in other languages but adapted for VBA, visit the additional string functions page with , StartsWith and EndsWith, Contains, PadLeft and PadRight, IsNullOrEmpty and more.
What is a String
The String variable type is used to hold strings of text. Strings are a sequence of characters: alphabetical, numbers, special characters in any combination.
It is good programming practice to declare a variable and its type before using it because it prevents mistakes (see Option Explicit):
Dim str As String
To give the variable a value the value has to be surrounded with double quotes:
str = "Some text" 'or' str = "100" ' Mind that the '100' is not a number but '100' as text.
|The Visual Basic interpreter tries to understand (resolve) any assignment of a variable given its type. The following would also be automatically converted to the string type: str = 100|
ActiveCell.Value = str 'You can also place text from the VBA Editor into an Excel spreadsheet cell' str = ActiveCell.Value 'And you can get text out of an Excel spreadsheet cell to the VBA Editor.
Concatenation means the joining of multiple strings into a single string. You can do this with the & (most commonly used) or + (alternative) symbol.
Dim str As String, str1 As String str = "abc" str1 = str & "_" & 12 & Date
... returns value for str1:
Note that VBA is not particular about the variable types you want to append, it will automatically convert the provided type to string in the process.
There are many VBA's built-in string functions that alter a string. Some of them we'll study here: Trim, LCase and UCase, Space, Replace and StrReverse
LCase(String): Returns the lower case of the specified string
Ucase(String): Returns the upper case of the specified string
When you have to compare two variable values it can be important to check if all values are in the same case for the cases that it is not important if a value is written with capital or not. Then you use Lcase or Ucase before the values.
Spaces - add or remove
Space(number) - fills a string with a specified number of spaces. Used in combination with/ concatenated with other variables via the '&'-symbol. Note that more often you will simply create a string containing the required number of spaces.
Dim str1 As String, str2 As String str1 = "FirstName" str2 = "LastName" MsgBox (str1 & Space(2) & str2) 'FirstName LastName' MsgBox (str2 & "," & Space(1) & str1) 'LastName, FirstName'
Other functions concerning spaces remove spaces. As variable-values with or without erroneous spaces differ it is important to check if there are any spaces in values.
|LTrim(String)||Returns a string after removing the spaces on the left side of the specified string.|
|RTrim(String)||Returns a string after removing the spaces on the right side of the specified string.|
|Trim(String)||Returns a string value after removing both leading and trailing blank spaces.|
Trim(" abc ") 'returns abc without spaces'
Syntax: Replace( string_to_search, string_to_replace, replace_with [start, [count, [compare]]] )
The arguments between the  are optional. Start: This is the position in string_to_search to begin the search. If this parameter is omitted, the Replace function will begin the search at position 1.
Count: This is the number of occurrences to replace. If this parameter is omitted, the REPLACE function will replace all occurrences of string_to_replace with replace_with.
Compare: This can be one of the following 2 values:vbBinary, the Compare Binary comparison or vbTextCompare, Textual comparison.
Replace("codevba", "vba", " VBA") 'Returns code VBA' Replace("codevba", "a", " Extra") 'Returns codevbExtra' Replace("John Doe", "o", "i") 'Returns Jihn Die
StrReverse("abc"). Reverses the specified string: cba
Substring functions return only a certain part of the original string. VBA has a neat collection of such procedures discussed below.
Left & Right
The functions Left and Right return the first or last number of characters:
Left("text_string", 3)'gives "tex"' Right("text_string", 3) 'gives "ing"'
Mid(string_to_search, start_position, number_of_characters)
For extracting a substring, starting at the start_position somewhere in the middle of a string. If you want to extract a substring from a string starting in the middle until the end of it you can omit the third argument.
Mid("text_string", 9, 2)'gives "in"
Mid("text_string", 3, 5)'gives "xt_st"
Length or Position
The Len(String) returns the length, the number of characters, of the string, including the blank spaces.
Len("String Manipulation!!") ' Result:21 '
A common use of Len is to check if the string is empty or not, and let that determine what to do:
If Len(str) = 0 Then GoTo NoInput Else 'process the string' End If
InStr( [start], string_to_search, substring, [compare] ) 'Returns the first occurence of the specified substring. Search happens from left to right.
On the first place you can give the startposition of the search, if omitted the search starts at the beginning. On the second place comes the text to search, and on the 3th place what you want to find. VBA will then give you an Integer back in return. This number is 0 if the string is not found. If the string is found then you get the location of the start of the string you were search for.
InStr(1, "codevbatool", "o")result: 2. You get the place of the first occurence from the left of the string of the 'o', not the count, nor the other 'o's
InStrRev(" "codevbatool", "o") result:10
Returns the first occurence of the specified substring. Search happens from Right to Left. You get the place of the first occurence from the right of the string, but counted from the left. it checks its position from the forward direction and gives its position as the result.
The practical use of InStrRev can be in finding the last index of a character inside a string.
|InStr||Returns the first occurence of the specified substring. Search happens from left to right.|
|InstrRev||Returns the first occurence of the specified substring. Search happens from Right to Left.|
|Lcase||Returns the lower case of the specified string.|
|Ucase||Returns the Upper case of the specified string.|
|Left||Returns a specific number of characters from the left side of the string.|
|Right||Returns a specific number of characters from the Right side of the string.|
|Mid||Returns a specific number of characters from a string based on the specified parameters.|
|Ltrim||Returns a string after removing the spaces on the left side of the specified string.|
|Rtrim||Returns a string after removing the spaces on the right side of the specified string.|
|Trim||Returns a string value after removing both leading and trailing blank spaces.|
|Len||Returns the lenght of the given string.|
|Replace||Returns a string after replacing a string with another string.|
|Space||Fills a string with the specified number of spaces.|
|StrComp||Returns an integer value after comparing the two specified strings.|
|String||Returns a String with a specified character the specified number of times.|
|StrReverse||Returns a String after reversing the sequence of the characters of the given string.|
Below image shows the Code VBA add-in support for VBA String procedures.