Working with Strings in VBA

The String data type is used to hold strings of text. Strings are a sequence of characters: alphabetical, numbers, special characters in any combination.

String variables are declared like Dim str As String

String variables (or constants or properties) get there actual value in an assignment statement:

  • from a string literal: str = "1st value!"
  • from another String variable: str = str1
  • from another String property: str = ActiveWorkbook.Name
  • from another String variable: str = str1

In this chapter, you'll find the most important VBA functions to

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.


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.
Note
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

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: abc_1230/08/2022

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.

String Manipulation

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


LCase("ALPHABET") 'Result: alphabet 

Ucase(String): Returns the upper case of the specified string


UCase("Alphabet") ' Result: ALPHABET

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.

string menu

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' 

Replace

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.

Examples:


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

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

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.

Examples:


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.

Example:


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(string1,string2[,start,[compare]])


 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.

Summary

Function NameDescription
InStrReturns the first occurence of the specified substring. Search happens from left to right.
InstrRevReturns the first occurence of the specified substring. Search happens from Right to Left.
LcaseReturns the lower case of the specified string.
UcaseReturns the Upper case of the specified string.
LeftReturns a specific number of characters from the left side of the string.
RightReturns a specific number of characters from the Right side of the string.
MidReturns a specific number of characters from a string based on the specified parameters.
LtrimReturns a string after removing the spaces on the left side of the specified string.
RtrimReturns a string after removing the spaces on the right side of the specified string.
TrimReturns a string value after removing both leading and trailing blank spaces.
LenReturns the lenght of the given string.
ReplaceReturns a string after replacing a string with another string.
SpaceFills a string with the specified number of spaces.
StrCompReturns an integer value after comparing the two specified strings.
StringReturns a String with a specified character the specified number of times.
StrReverseReturns 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.

support for VBA String procedures