String expressions

“String” is a geek word for text. A string of letters. A string expression is about text, like characters (ABC… abc…), digits (0123456789), and special characters (!#*>]{…).

String constants

Remember that numeric constants are things like 33, -1.009, and 32.22. Numeric constants don’t change.

There are string constants as well. They have “ around them, like "This is a string constant". The stuff inside the “ “ will never change.

Another example:

line1 = "Who let the dogs out?"

The thing in the quotes is a constant. It will never change. The quotes let VBA know where the string constant starts and ends. This…

line1 = Who let the dogs out?

… wouldn’t work. No quotes. VBA might think that Who is a variable. Then it gets let, and doesn’t know what to do. So the program crashes.

The simplest string constant is:

""

That’s two double quotes, with nothing between them. Not even a space. This is called an empty string.

Here’s some code you saw before:

MsgBox "Sorry, amount must be a number."

The MsgBox statement takes a string expression. A string constant is a type of string expression.

String operators

Numeric operators are things like +, -, *, etc. There’s only one string operator: &. It sticks strings together. For example:

Dim s1 as String
Dim s2 as String
s1 = "Buffy"
s2 = "slays"
MsgBox s1 & s2

This would show:

MsgBox

Oops. We need a space in there. New code:

Dim s1 as String
Dim s2 as String
s1 = "Buffy"
s2 = "slays"
MsgBox s1 & " " & s2

Better

The geek name for & is the “concatenation operator.”

You can use & with numbers and strings, but the result is always a string. For example:

Dim number As Single
number = Cells(1, 2)
MsgBox "The square root of " & number & " is " & Sqr(number) & "."

number and Sqr(number) are both numeric expressions. Put 25 in cell B1, and here’s what you’ll see:

25

String functions

Remember that a function is some code with a name. You send it data, and it returns a result. Here are some string functions built into VBA:

LCase() Lowercase LCase("FRankie Boyle") is "frankie boyle"
UCase() Uppercase UCase("FRankie Boyle") is "FRANKIE BOYLE"
Trim() Removes spaces from start and end Trim(" Taylor Swift ") is "Taylor Swift"
Replace() Replace parts of a string Replace("Can I haz milk?", "haz", "have") is "Can I have milk?"
InStr() Search for a string inside a string InStr("I am here.", "m") is 4
Left() Return the left part of a string Left("Honey badger", 4) is "Hone"
Right() Return the right part of a string Right("Wombat", 3) is "bat"
Mid() Return the middle part of a string Mid("Lady Gaga", 6, 2) is "Ga"
Len() Return the length of a string Len("JenLaw") is 6

For Mid(), the third parameter is optional. Without it, the function returns all the text to the end of the string. For example,…

Mid("Skyrim", 4)

… is "rim".

Business software does a lot of string processing.

Example: Switching first and last names

Say you had a database that stored people’s names with last name first, like this:

Mathieson, Kieran

You want a program that sends email to people. You wouldn’t want the email greeting to be:

Dear Mathieson, Kieran,

You’d want it to be:

Dear Kieran Mathieson,

Let’s write a program to change the names around. The worksheet would start like this:

Start

Click Run, and get:

End

How to think about this?

If we knew where the comma was, then we could grab the text to the left. That would be the last name.

M  a  t  h  i  e  s  o  n  ,     K  i  e  r  a  n
1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17
Last name                ← ↑

The comma is at character 10. The last name is characters 1 to 9.

This works for any last name.

S  m  i  t  h  ,     S  a  r  a
1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17
Last name   ←  ↑
M  a  l  f  o  y  ,     D  r  a  c  o
1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17
Last name    ←    ↑

The function InStr() will find the position of a character in a string.

InStr("Mathieson, Kieran", ",") is 10.

InStr("Smith, Sara", ",") is 6.

InStr("Malfoy, Draco", ",") is 7.

We could use variables, like this:

firstLast = "Butcher, Rhea"
commaPos = InStr(firstLast, ",")
MsgBox "The comma is character number " & commaPos

commaPos is short for commaPosition.

The function Left() returns a number of characters from the left of a string.

Left("Spitting monkey", 4) is Spit.

We can use that to find the last name:

firstLast = "Esposito, Cameron"
commaPos = InStr(firstLast, ",")
lastName = Left(firstLast, commaPos - 1)
MsgBox "The last name is " & lastName

Notice commaPos - 1. That’s what gives us the characters to the left of the comma. This…

lastName = Left(firstLast, commaPos)

… wouldn’t work. It would include the comma in the last name. If you don’t see why, try it in a VBA program.

What about the first name? That’s to the right of the comma:

M  a  t  h  i  e  s  o  n  ,     K  i  e  r  a  n
1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17
                           ↑ → First name

The Mid() function returns part of a string from a certain character position onward.

Mid("Spitting monkey", 8) is g monkey.

We could do this:

firstLast = "Esposito, Cameron"
commaPos = InStr(firstLast, ",")
lastName = Left(firstLast, commaPos - 1)
firstName = Mid(firstLast, commaPos + 2)
MsgBox "The last name is " & lastName

Why + 2? So we skip the space after the comma. That would work.

In practice, it would be easy for people to forget to type the space after the comma. The + 2 would lose the first character of the first name. Don’t call Cameron ameron. She’ll sarcasm you into a trembling ball of shame.

The Trim() function comes in handy here. It strips spaces off the start and end of a string. Here’s the final code for the program that makes this work:

End

Option Explicit
Private Sub cmdRun_Click()
    Dim lastFirst As String
    Dim lastName As String
    Dim firstName As String
    Dim commaPos As Integer
    Dim greeting As String
    'Get input.
    lastFirst = Cells(1, 2)
    'Find the comma's position.
    commaPos = InStr(lastFirst, ",")
    'Letters to the left of the comma are the last name.
    lastName = Left(lastFirst, commaPos - 1)
    'Letters to the right of the comma are the first name.
    firstName = Mid(lastFirst, commaPos + 1)
    'Remove extra spaces.
    lastName = Trim(lastName)
    firstName = Trim(firstName)
    'Compute greeting.
    greeting = "Dear " & firstName & " " & lastName & ","
    'Output.
    Cells(3, 2) = greeting
End Sub

This works by finding the position of a character (a comma) in a string, then doing arithmetic to break up the string into pieces. This kind of programming is common in business.

Exercises

Fill in the blank

Parsing stock data

Data analysts write programs to sift through data. One aspect of that is parsing, that is, extracting the data you want from a bunch of data with a particular format.

Here’s code that parses data.

Dim rawData As String
Dim cc As String
Dim ccl As Integer
Dim temp As String
Dim result As String
rawData = "dwe|334.25|faa|32.5|faj|4.5"
cc = "faa"
ccl = InStr(rawData, cc)
temp = Mid(rawData, ccl + Len(cc) + 1)
ccl = InStr(temp, "|")
result = Left(temp, ccl - 1)

What is the value of result at the end of the program?

Work it out on paper first, before you try it in Excel.

Your answer:

Exercise: Parse scientific names
The scientific name of a species has two components: genus, and species. For example, the blue whale is balaenoptera musculus.

Write a program to parse a scientific name, and break it into separate components. For example, start with this:

Start

Run, and get this:

End

The usual coding standards apply.

Upload your solution.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

A string can have letters (ABC… abc…), digits (0123456789), and special characters. “Pintrest” is a string constant. The simplest string constant is "", the empty string.

The concatenation operator, &, sticks strings together.

There are many string functions. They change case, strip spaces, find one string inside another, return parts of a string, etc.