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
- Split Function
- Numbers in Strings
- Case Conversions
- Substrings and Positions
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.
|String Function||Type of Operation||Description|
|&||Concatenation||Combines the strings on either side|
|+||Concatenation||Concatenates (combines) the strings on either side, if and only if they are both strings. If you try to concatenate a string holding a number and a number (integer, double, etc), the + operator will add your numbers instead of concatenating them.|
|Join||Concatenation||Joins the strings in the input array, optionally delimited by a string of your choice (strings can be punctuation)|
|Split||Reverse Concatenation||Splits up the input string into an array. Splits based on the chosen delimiter and each item in your string becomes its own element in the array.|
|LTrim, RTrim, Trim
||Space Removal||Removes all leading, trailing, or leading and trailing spaces from a string|
|Space||Space Addition||Creates a string with the specified number of spaces|
|Replace||Substitution||Substitutes some string for another string. Often used for removing spaces or systematically changing strings|
|Val||Type Conversion||Converts a string into a decimal number|
|CInt||Type Conversion||Converts a string into an integer using banker's rounding, if necessary|
|CStr||Type Conversion||Converts a number to a string|
|CDate||Type Conversion||Converts a string to a date, so you can use date arithmetic and apply date functions.|
|StrComp||Comparison||Compare two input strings, can be case sensitive or insensitive|
|LCase, UCase||Case Conversion||Converts a string to all lower or upper case, which can be useful when comparing VBA strings|
|StrConv||Case Conversion||Another way to convert ASCII strings to upper case, lower case, or proper case (first letter of each word capitalized). The StrConv function also lets you convert from ASCII to Unicode and back.|
||String Extraction||Returns the substring of a given length. The length is counted from the beginning or end, depending on whether you're using the Left or Right function.|
|Mid||String Extraction||Returns the string of optional given length, starting at the given position. Always counts from the beginning.
||Returns the starting position (index starting at 1) of the specified string within another string, or 0 if the substring is not found in the main string.|
|InStrRev||String Extraction||Returns the starting position of the specified substring, but starts counting from the end of your string.|
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.
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
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
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
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
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
- Hello World
- 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
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:
Trim (as they “trim” the spaces off the string).
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.
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
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
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
If you don’t want to deal with decimals, you can always round up/down by using the
my_num3 = CInt(my_num) + CInt(my_num2)
Now we get
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
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.
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
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
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.
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.
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.