Working with Strings in VBA

The String data type is used to hold strings of text. A string value is a sequence of characters: alphabetical, numbers, special characters in any combination. When assigning a specific value to a string constant the value must be surrounded by double quotes.

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

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

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.

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.

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.

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

Search

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'     

The practical use of InStrRev can be in finding the last index of a character inside a string.

Summary

Below image shows the Code VBA add-in support for VBA String procedures.