VBA String Functions and how to use them

This tutorial describes VBA string functions, explains how to use basic VBA string functions in your own macros, and guides you to detailed tutorials covering advanced VBA string functions. It’s everything you need to know about working with strings in VBA, all in one place.

One of the most basic types of data structures is the string. A VBA string is just like a string in any other programming language; it’s a character or a series of characters set next to each other in a particular order. Unlike some other data structures, like VBA dictionaries, the order is important. Clearly the strings, “hello world” and “dello whorl” are, and should be, treated differently.

VBA offers several ways to manipulate these basic data structures. We’ll describe the main VBA string functions in this tutorial. You can create some really advanced VBA string functions using things like VBA RegEx, which can add significant firepower to your string-manipulation toolbox.

• VBA String Functions
• Concatenation
  • Join Function
• Split Function
• Spaces
  • Add Spaces
  • Remove Spaces
• Numbers in Strings
• Case Conversions
• Substrings and Positions
  • Padding for Convenience
• Conclusion

VBA String Functions

Here’ a list of the most commonly used VBA string functions and a description of what they do. This is an incredibly helpful table, so I encourage you to refer to it any time you’re working with strings in your VBA macros. The table will link you to detailed tutorials about some of the string functions so you can read more about how to use them.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

VBA Concatenation in Two Flavors

You can concatenate VBA strings in two ways: via the ampersand/plus and via the Join function. The first requires you to add an ampersand (&) or a plus sign (+) between every string. For short sets, (one or two small strings), this isn’t so difficult.

Warning: The plus sign does NOT behave the same way as the ampersand. The ampersand should almost always be used. Take a look at my full VBA Concatenation tutorial to understand what I mean.

Sub concat_with_amps()
first_string = "Hello World."
second_string = "Hello Friend."

third_string = first_string & second_string

MsgBox (third_string)

End Sub

Our output? Hello World.Hello Friend.

The first thing you may notice is there is no space. For that, you can add your own space using, like this:

third_string = first_string & " " & second_string

Now we get Hello World. Hello Friend..

Now imagine you have 50 strings. You can imagine how tedious this would be, since you’d have to type a lot of " " and & in your macros. Yes, you could use a for-loop to go through each iteration and build the string that way, but there is a better method.

The VBA Join Function

The Join function is the first of several VBA string functions we’ll discuss. If you happen to have all your strings in an array already, which very well might be the case if you read several strings from a sheet into an array, you can concatenate them and even add a delimiter between them using the VBA Join function.

Sub concat_with_join()
string_arr = Array("Hello World.", "Hello Friend.")

third_string = Join(string_arr, " ")

MsgBox (third_string)

End Sub

By specifying the space in the optional [Delimiter] argument, you’ll automatically separate every string with that delimiter. You could even do it with other strings, like so:

Sub concat_with_join_and_smiley()
string_arr = Array("Hello World.", "Hello Friend.", "Hello All.")

third_string = Join(string_arr, " :) ")

MsgBox (third_string)

End Sub

Which will yield us Hello World. :) Hello Friend. :) Hello All.

Separating Strings with Split

Now let’s say you want to extract the sentences from your longer string “Hello World. Hello Friend.” One way to do it is to use our second VBA string function: the Split function. Take a look at the macro example:

Sub separate_strings()
full_string = ("Hello World. Hello Friend.")

string_arr = Split(full_string, ".")

End Sub

Now, the array string_arr holds three items:

1. Hello World
2. Hello Friend

The first two make sense, but what happened with the third? Well, Split found the last period and wrote whatever came after the period as another string. It just happened to be empty.

You can use VBA UBound to check if the last item is empty, and if it is, just reference the preceding inputs in the array:

last_item = UBound(string_arr)

If string_arr(last_item) = "" Then last_item = last_item - 1

Using last_item as your final index will ignore the empty string at the end.

You can see more about splitting in our full VBA Split tutorial.

Adding and Removing Spaces

Sometimes we need to work with padded strings. Padded strings are strings with extra spaces embedded within them, like at the beginning or end. The function to add spaces to a string is Space. Similarly, there are three functions designed to remove spaces from a string: LTrim, RTrim, and Trim (as they “trim” the spaces off the string).

The Space function

To add a number of spaces, you can simply write my_padded_string = Space([num]) where [num] is the number of spaces.

Why would you want to do this? Well, it is one way to add the delimiter in the Join function:

Sub concat_with_join_and_space()
string_arr = Array("Hello World.", "Hello Friend.")

third_string = Join(string_arr, Space(1))

MsgBox (third_string)

End Sub

It can also be useful for placeholders when data is not available yet, but you know exactly how long the string will be. Sometimes programs require a minimum length of string before they execute. Space can help you circumvent that requirement until you have data to fill up the string.

It is also useful if other code is position-based and you need to always have 10 characters, for example, before another part. We’ll talk more about that in the Substring section, but it’s very common to have to process or generate fixed width data. Creating algorithms with the VBA space function is perfect for that.

Removing Spaces

You can use the various trim functions to remove spaces. We’ve talked about how to use the VBA RTrim function, in combination with other functions, to remove whitespace characters other than simple spaces.

The trim functions are especially useful if you have user-entered data. Often users will add extra spaces for whatever reason. Whatever their reason, you might need to remove these extra spaces from a string.

LTrim removes spaces from the left
RTrim removes spaces from the right
Trim removes spaces from both sides

You can also use the VBA Replace function to remove spaces. As a matter of fact, you can use the VBA Replace function to replace any substring with a new substring.

Numbers in Strings

Ah, the deceptive number as a string. If you are not careful, these kinds of hidden issues will throw all kinds of mismatch errors your way. We warned you about this when talking about concatenating strings!

If you have just the number in a cell, there’s a good chance it’s formatted as text. If it is formatted as text, you ought to convert it before using it for math operations.

Usually we want to use integers (especially if you’ll be using it in a for-loop) or floating point numbers (i.e., decimal values).

The program below will not output 30.84:

Sub convert_strings_to_numbers_broken()
my_num = "10.5"
my_num2 = "20.34"
my_num3 = my_num + my_num2
End Sub

Instead, we get 10.520.34 as my_num3, which is not actually a decimal number. Why is that? Well, VBA is treating these as strings!

This is why you need to convert your strings to numbers. If you don’t, VBA might not know you want to use them as numbers. To VBA, they look exactly like strings. Likewise, there are instances where VBA will try to convert your strings to numbers for you, whether you want it to or not. This is exactly why you ought to be explicit when converting your strings.

Sub convert_strings_to_numbers_fixed()
my_num = "10.5"
my_num2 = "20.34"
my_num3 = Val(my_num) + Val(my_num2)
End Sub

Now we get the desired 30.84 result.

If you don’t want to deal with decimals, you can always round up/down by using the CInt function:

my_num3 = CInt(my_num) + CInt(my_num2)

Now we get 30. Note, you wouldn’t be able to write your function like CInt(my_num + my_num2), since the “numbers” would still be treated as strings during the “addition” concatenation part. We have a thorough VBA CInt tutorial if you want to read more about converting strings (and other data types) to integers.

To reverse the conversion (i.e., number to string), check out our VBA CStr tutorial. The CStr function is exactly what you want to use to convert a number to a string for use in your string manipulation routines.

Consistency in Comparisons (and Aesthetics)

One of the most useful things a computer can do is compare large amounts of data very quickly. Computers revel in such tedium, and they have very strong skills in the area.

Foreword: If you want to compare strings without this step, you can do so by using the VBA StrComp function.

You can easily compare strings in if-statements like this:

Sub str_compare()
my_string = "Hello World."
my_string2 = "HELLO WORLD."
If my_string = my_string2 Then MsgBox ("Hello Friend.")
End Sub

Unfortunately, this will never trigger the MsgBox. Why not? Because these strings aren’t actually the same. We can make them the same with UCase or LCase.

To ensure consistency in your comparison functions, it is good to either use the StrComp function with case insensitivity or convert all strings to the same case, like we did in our compare cells using VBA tutorial.

Ensuring letter case consistency is as simple as this:

Sub str_compare_ucase_consistency()
my_string = "Hello World."
my_string2 = "HELLO WORLD."
If UCase(my_string) = UCase(my_string2) Then MsgBox ("Hello Friend.")
End Sub

You could implement LCase instead of UCase if you prefer.

Extracting Substrings

Sometimes we really don’t care about the whole string. We just want to focus on a part of it. If you have tidy, pre-formatted and cleansed data, you can use the Left, Right, or Mid functions to do the extraction.

These functions take a string as an input and output a string of a specified length. This is paricularly useful if you have strings that always start with, say, a timestamp.

Sub left_extraction()
my_string = "2018-05-20-082315 1BTC 5235USD 100BTCVOL"
the_date = Left(my_string, 17)
End Sub

This is some (fictitious) trade information for Bitcoin on May 20, 2018 at 8:23:15 AM UTC.

Since dates will always be in this format, you can always use the Left function to easily extract the date. Extracting any other information (the price, the volume) is not easily done with these substring functions, since their positions can move. You have a couple better options. One thing you could do use use the VBA Split function we discussed earlier to split the string at each space. Another way to do it is to use the VBA InStr function to find the tag (BTC, USD, BTCVOL) and chop the string up into pieces based on positions and lengths.

If you really wanted to use Left, Right, and InStr, you could do it this way:

Sub convoluted_extraction()
trade_string = "2018-05-20-082315 1BTC 5235USD 100BTCVOL"
trade_ex_date = Right(trade_string, Len(trade_string) - 17) 'remove date
btc_tag_pos = InStr(trade_ex_date, "BTC")   'find start of BTC
btc_amount = Left(trade_ex_date, btc_tag_pos - 1)   'take the string before the BTC marker
End Sub

Of course, this only works if the structure of the string doesn’t change. If your API starts outputting date-vol-price formats, you’ll be capturing the vol and the price, not just the price. Hence leveraging VBA Regular Expressions is much better in this situation.

We didn’t use it in our examples, but the VBA Mid function is particularly useful. We’ve even developed a user-defined function to make extracting substrings even easier.

Using Space to Standardize Data

One reason to use the Space function is to standardize your data. If you are generating the data yourself, you might have a tool to capture names and jobs, like this:

Sub create_name_job_string()
user_name = InputBox("Enter your name")
user_job = InputBox("Enter your job")

name_and_job = "Name: " & user_name & " Job: " & user_job
End Sub

Then you might have some function to extract the job, like this. This is a silly example, I know, but stick with me.

Function get_job(name_job_string)
get_job = Mid(name_job_string, 50)
End Function

Unfortunately, unless your user’s name is exactly 37 characters, you’re going to end up with something that is not a job. (That is 37 characters for the name plus 6 for “ Job: “, plus 6 for “Name: “, and finally plus one more to start after the space in “Job: “).

The easiest way to make this function work properly is to implement the Space function to pad everything first.

spaces_required = 37 - Len(user_name)
user_name = user_name & Space(spaces_required)

Now your string for the job will always start at 50, and your function will work fine. When reading back these values, remember to use the trim functions to get cleaner results.

Conclusion

These are some of the main string functions with examples and links for how to use them. If you want some more details, you can see the list of string articles on this page. We’ve already written about other common ones, like Replace, InStrRev, and StrConv.

String manipulation is one of the most basic computer science operations, and their natural representations in computing makes them easy to manipulate. Understanding the functions that are associated with them is an excellent way to build a solid foundation in VBA.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.