Manipulating text is a frequent programming task. Fortunately, VBA has numerous functions that make this easy work. The purpose of this week’s column is to illustrate the use of some of the text functions that I tend to use the most.
Text in VBA is usually stored in string variables. When discussing text manipulation, then, it is common to refer to text as a string. Perhaps the most common need when it comes to manipulating strings is to concatenate two strings together. For this, you don’t even need to use a function — use the ampersand (&) character. Listing 1 demonstrates string concatenation.
Listing 1: A demonstration of string concatenation.
Sub StringConcatenation()
Dim sFirstName As String
Dim sLastName As String
Dim sFullName As String
sFirstName = "Ronald"
sLastName = "McDonald"
Debug.Print "His name is " & sFirstName & " " & sLastName
sFullName = sFirstName & " " & sLastName
Debug.Print "His name is " & sFullName
End Sub
— The output of this listing is: —
His name is Ronald McDonald
His name is Ronald McDonald
After concatenation, the other basic needs relate to simple string modification such as stripping characters from the beginning or end of a string. The following table documents the basic string modification functions. These functions are demonstrated in Listing 2.
Function Name and Purpose
Left (string, n) Returns the n leftmost characters from a string
Right(string, n) Returns the n rightmost characters from a string
Mid (string, starting at, n) Returns n characters from a string starting with the character in the "starting at" position
LTrim (string), RTrim (string), Trim (string) Removes leading (LTrim), trailing (RTrim), or leading and trailing (Trim) white space
LCase (string), UCase (string) Converts a string to lower case (LCase) or upper case (UCase)
Listing 2: A demonstration of string manipulation.
Sub StringManipulation()
' Using the Left function
Dim sZipCode As String
sZipCode = "55021-0000"
Debug.Print "The short zip code is: " & Left(sZipCode, 5)
' Using the Mid and Right functions
Dim sPhoneNumber As String
sPhoneNumber = "123-555-1234"
Debug.Print "The local prefix is: " & Mid(sPhoneNumber, 5, 3)
Debug.Print "The last four digits are: " & Right(sPhoneNumber, 4)
' Using the Trim functions
Dim sPaddedString As String
sPaddedString = " Interesting Text "
Debug.Print "Using LTrim:***" & LTrim(sPaddedString) & "***"
Debug.Print "Using RTrim:***" & RTrim(sPaddedString) & "***"
Debug.Print "Using Trim:***" & Trim(sPaddedString) & "***"
' Using LCase and UCase
Dim sMixedCase As String
sMixedCase = "New York, NY"
Debug.Print UCase(sMixedCase)
Debug.Print LCase(sMixedCase)
End Sub
— The output of this listing is: —
The short zip code is: 55021
The local prefix is: 555
The last four digits are: 1234
Using LTrim:***Interesting Text ***
Using RTrim:*** Interesting Text***
Using Trim:***Interesting Text***
NEW YORK, NY
new york, ny
The ability to employ basic string manipulation opens up a world of possibilities beyond what you can achieve with simple macro recording. Next week I’ll continue this discussion by explaining other text manipulation functions and techniques.