Strings in VBA

String is the data type indicated to store text in VBA.

    Dim VarText As String 'Declaration of a variable of the data type String

To store text information in a String variable, the content must be enclosed in double quotation marks (" "):

    Dim VarText As String
    VarText = "Some Name"

Although it is possible to store numeric information in text format, they will not behave in the same way as in numeric format. This will modify and limit their acceptable operators.
      Dim VarText As String
      Dim VarNum As Single

      VarText = "6" + "3"
      MsgBox VarText 'This will result in "63" in text format

      VarNum = 6 + 3
      MsgBox VarNum 'This will result in 9

      VarText = "6" / "3"
      'This will generate an error: this operator does not exist for String

      VarNum = 6 / 3 
      'This will make the division normally

The following are the main predefined functions for manipulating Strings in VBA.

Key VBA Text Functions

VBA UCase and LCase

The functions UCase and LCase make a string in uppercase and lowercase letters, respectively.

Function	String	Example	Result
UCase	Makes a string in uppercase letters	`Sub UppercaseLetters() Text = "Text Example" MsgBox UCase(Text) End Sub`	TEXT EXAMPLE
LCase	Makes a string in lowercase letters	`Sub LowercaseLetters() Text = "Text Example" MsgBox LCase(Text) End Sub`	text example

UCase and LCase are important functions for data standardization, since not all of your sources will be in the same case (uppercase or lowercase).

VBA StrConv

Another function to change the text case is the StrConv. (which can work as UCase, LCase and can also make the first letters uppercase.

When using StrConv(string, conversion) it is necessary to include the desired conversion type as an argument.

Valor (conversion)	Equivalent	Description
StrConv(string,1)	vbUpperCase	Converts everything to uppercase
StrConv(string,2)	vbLowerCase	Converts everything to lowercase
StrConv(string,3)	vbProperCase	Converts the first letters to uppercase and the others for lowercase

Here is an example of StrConv using vbProperCase:

Function	Argument	Example	Result
StrConv	vbProperCase	`Sub FirstUppercase() Text = "text example" MsgBox StrConv(Text, 3) 'StrConv(string, conversion) End Sub`	Text Example

VBA LTrim, RTrim and Trim

The LTrim, RTrim and Trim functions are used to remove spaces at both ends of a String:

Function String Example Result

Function	String	Example	Result
LTrim	Removes the spaces in the left end	`Sub LeftEnd() Text = " Text Example " MsgBox ("Before using LTrim: " & Text & "..." _ & vbCrLf & vbCrLf & _ "After using LTrim: " & LTrim(Text) & "...") End Sub`	Text Example ...
RTrim	Removes the spaces in the right end	`Sub RightEnd() Text = " Text Example " MsgBox ("Before using RTrim: " & Text & "..." _ & vbCrLf & vbCrLf & _ "After using RTrim: " & RTrim(Text) & "...") End Sub`	Text Example...
Trim	Removes the spaces in both ends	`Sub Spaces() Text = " Text Example " MsgBox ("Before using Trim: " & Text & "..." _ & vbCrLf & vbCrLf & _ "After using Trim: " & Trim(Text) & "...") End Sub`	Text Example...

LTrim

Removes the spaces in the left end

Sub LeftEnd()

Text = "   Text Example   "

MsgBox ("Before using LTrim: " & Text & "..." _
 & vbCrLf & vbCrLf & _
 "After using LTrim: " & LTrim(Text) & "...")

End Sub

Text Example ...

RTrim

Removes the spaces in the right end

Sub RightEnd()

Text = "   Text Example   "

MsgBox ("Before using RTrim: " & Text & "..." _
 & vbCrLf & vbCrLf & _
"After using RTrim: " & RTrim(Text) & "...")

End Sub

Text Example...

Trim

Removes the spaces in both ends

Sub Spaces()

Text = "   Text Example   "

MsgBox ("Before using Trim: " & Text & "..." _
 & vbCrLf & vbCrLf & _
 "After using Trim: " & Trim(Text) & "...")

End Sub

Text Example...

The "..." was inserted after the variable to show the reduction of spaces.

vbCrLf is a function to add a line ("Enter") to the String.

VBA Len

Len is a function that returns the total number of characters in a String.

Function	String	Example	Result
Len	Total characters	`Sub Length() Text = "Text Example" MsgBox Len(Text) End Sub`	12

All characters are counted, including whitespace characters.

Len can be used as a simple way to validate information.

Sub orderNumber
    Dim onumber As String
    onumber = Inputbox("What is your order number?")
    If Len(onumber) = 10 Then
    'All numbers must have 10 characters
        MsgBox "We will process your request shortly."
    Else
        MsgBox "Invalid code. Please check the inserted number"
    End If
End Sub

VBA InStr and InStrRev

The InStr and InStrRev functions search within a String a given occurrence and return (counting from left to right in number of characters) the position it appears.

Function	String	Example	Result
InStr	Search the occurrence position from left to right	`Sub WithinTheString() Text = "text example" MsgBox InStr(Text, "t") End Sub`	12
InStrRev	Search the occurrence position from right to left	`Sub WithinTheStringReverse() Text = "text example" MsgBox InStrRev(Text, "t") End Sub`	15

These functions can be very useful when used together with Left and Right, as we will see later.

In both cases, InStr and InStrRev return:

The position number, counting from the beginning of the String, that is, counting from left to right.

The position of the first occurrence within the String,(InStr the left most, InStrRev the right most), starting from left to right.

If the returned value is 0, it means that the searched occurrence was not found within the String.

VBA Left, Right and Mid

The Left, Right and Mid functions are used to extract parts of a line of text (String).

    MsgBox Left("Text example", 3) 'Retornará "Tex"
    MsgBox Right("Text example", 3) 'Retornará "ple"
    MsgBox Mid("Text example", 3, 4) 'Retornará "xt e"

Left, Right and Mid functions (such as Len, InStr and InStrRev) can be used in conjunction with other functions for better results.

Function String Example Result

Function	String	Example	Result
Left	Extracts characters to the left of a given position	`Sub LeftCharacters() Text = "Text Example Here" WhiteSpace = InStr(Text, " ") 'Detects the position of the first whitespace character (from the left) 'WhiteSpace = 5 MsgBox Left(Text, WhiteSpace - 1) 'Left(string, [length]) 'To extract the first word End Sub`	Exemplo
Right	Extracts characters to the right of a given position	`Sub RightCharacters() Text = "Text Example Here" WhiteSpace = InStrRev(Text, " ") 'Detects the position of the first whitespace character (from the right) 'Whitespace = 13 WhiteSpace = Len(Text) - WhiteSpace 'To extract the last word 'WhiteSpace = 17 - 13 MsgBox Right(Text, WhiteSpace) 'Right(string, [length]) End Sub`	Texto
Mid	Extracts characters from within the string, between given positions	`Sub CharactersMid() Text = "Text Example Here" Spc1 = InStr(Text, " ") 'Spc1 = 5 Spc2 = InStrRev(Text, " ") 'Spc2 = 13 Lngth = Spc2 - Spc1 MidText = Mid(Text, Spc1, Lngth) 'Mid(string, start, [length]) MsgBox Trim(MidText) End Sub`	de

Left

Extracts characters to the left of a given position

Sub LeftCharacters()

Text = "Text Example Here"

WhiteSpace = InStr(Text, " ") 'Detects the position of the first whitespace character (from the left)
'WhiteSpace = 5

MsgBox Left(Text, WhiteSpace - 1)
'Left(string, [length])
'To extract the first word

End Sub

Exemplo

Right

Extracts characters to the right of a given position

Sub RightCharacters()

Text = "Text Example Here"

WhiteSpace = InStrRev(Text, " ") 'Detects the position of the first whitespace character (from the right)
'Whitespace = 13
WhiteSpace = Len(Text) - WhiteSpace
'To extract the last word
'WhiteSpace = 17 - 13

MsgBox Right(Text, WhiteSpace)
'Right(string, [length])

End Sub

Texto

Mid

Extracts characters from within the string, between given positions

Sub CharactersMid()

Text = "Text Example Here"

Spc1 = InStr(Text, " ")
'Spc1 = 5

Spc2 = InStrRev(Text, " ")
'Spc2 = 13

Lngth = Spc2 - Spc1
MidText = Mid(Text, Spc1, Lngth)
'Mid(string, start, [length])

MsgBox Trim(MidText)

End Sub

In the arguments of the Mid function the initial position and the number of characters to be extracted must be indicated.

VBA Replace

The Replace function is used to swap parts of the text with a desired one.

    Dim Text As String
    Text = "Always, Always study VBA"

    MsgBox Replace(Text, "Always", "Super")

VBA String to numeric

It is possible to convert a data with type String to numeric with the help of functions:

Dim VarInt As Integer
VarInt = CInt("34") 'Converts "34" to the integer 34
MsgBox VarInt+3 'Returns 37

Function	Definition
CByte	Converts a String to type Byte
CInt	Converts a String to type Integer
CLng	Converts a String to type Long
CSng	Converts a String to type Single
CDbl	Converts a String to type Double
CCur	Converts a String to type Currency
CDec	Converts a String to type Decimal

You can convert a number to a String using CStr

VarText = CStr(33) 'Converts 33 to "33"
MsgBox VarText & "a" 'Returns "33a"

Consolidating Your Learning

Suggested Exercise

Sum of Powers

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

FIRST STEPS

FUNDAMENTALS

RANGE

USERFORM

DEEPENING