02 Oct Working with Strings in Excel VBA
Howdee! If you’ve ever written any VBA code, you’ve likely had to edit strings in Excel VBA. It can be challenging and more than a little frustrating, especially if you’re not familiar with the tools available to you within the framework. Have you ever had to transform a string of last name comma first name to a traditional full name field in code? Ever had to pull a piece of information out of a string of dozens of characters? Then this article is for you. With that done, let’s dive right in!
VBA InStr & InStrRev Functions
These two functions are, in my opinion, the most crucial text functions to understand when it comes to getting data from inside a string of text in Excel. Once you understand these, you can begin to combine them with other functions to write truly powerful text parsing code.
The Instr function returns the starting position, as a number, of a string within a string of text. For example, we could search the string “I owe you 25 dollars on September 25th” for the literal string “25”. Depending on which function you use you would get different results! Let’s examine the below code:
Sub Instr_InstrRev_Example()
Dim stringExample As String
stringExample = "I owe you 25 dollars on September 25th"
MsgBox InStr(1, stringExample, "25") 'returns 11
MsgBox InStrRev(stringExample, "25") 'returns 35
End Sub
The first message box will display “11” and the second will display “35”. This is important to think about when writing your code, if you need the first or last occurrence of a string within a string, then these functions will serve you very well. In the interest of being thorough, here is the syntax for both functions:
InStr( [start], string, substring, [compare] )
Start is an optional parameter that tells the function which character to begin searching. If you omit it, the compiler will start at the first character. String represents the string you’re searching while substring is the string you’re searching for. The compare parameter is also optional and I’ve rarely had need to use it so I won’t confuse you with it now but you’ll almost always use text compare.
InStrRev (StringCheck, StringMatch, [Start], [Compare])
StringCheck is the string you’re searching while StringMatch is the string you’re searching for. The start variable is optional as it was in InStr. The difference is it starts at the end of the string if omitted. Compare is the same as from the previous function also.
You might be asking yourself what use the VBA Instr and InstrRev functions are if they only return the starting position of a first or last occurrence of a substring. Once we introduce the next function, it will begin to make more sense.
VBA Mid Function
The Mid VBA function extracts a substring from a string based on a starting position and length that you specify. Before diving into examples, let’s look at the syntax:
Mid(text, start_position, number_of_characters)
Text is the full string of text from which you want to remove a substring. Start_position is an integer that represents the starting position of the first character of the string you wish to remove. Finally, number_of_characters is an integer representing the number of characters you wish to return.
Continuing with the sentence we used in the prior example, let’s examine the following code:
Sub MidExample()
Dim stringExample As String
stringExample = "I owe you 25 dollars on September 25th"
MsgBox Mid(stringExample, 1, 5)
End Sub
In this example, our Mid VBA function would return the string “I owe”. That’s because we have told the function to start at the first character in the string and return 5 characters from that point, including the starting character. Combining this function with the two InStr functions from the prior example, you can dynamically tell your Mid function where to start returning text from within a string. Using these functions together is how I primarily handle working with strings in Excel VBA.
VBA Left & Right Functions
If you’ve been using Excel for any period of time, you’ve probably either seen or used the Left and Right worksheet functions. Both of these functions exist in VBA and the syntax is the exact same. So, if you’re familiar with these functions, you can probably skip ahead to the next section but, in the interest of thoroughness, I want to cover these functions for folks who may not have used them before.
By their names, you can probably infer what these two functions do. The Left function returns a specific number of characters from the beginning of a string, while the Right function returns a specified number of characters form the end of a string. The syntax for them looks like this:
Left(text, number_of_characters)
Right(text, number_of_characters)
In both functions, the text input is the string you’re wanting to return a portion of, while the number_of_characters input is an integer representing the number of characters you wish to return. Let’s use our $25 sentence to examine the results in the following code:
Sub LeftRightExample()
Dim stringExample As String
stringExample = "I owe you 25 dollars on September 25th"
MsgBox Left(stringExample, 5)
MsgBox Right(stringExample, 5)
End Sub
In this example, the left function would return “I owe” while the Right example would return “ 25th”. The VBA functions Left & Right are very useful when you need to return the beginning X number of characters or the ending X number of characters from a string. It can also be used to return the beginning/ending portions of substrings when other functions are nested inside the text input. While I use these less than the previous two, they’re incredibly useful when just grabbing the beginning or ends of strings in Excel VBA.
VBA Replace Function
The last function I want to cover in depth is the VBA Replace function. As you might expect from its name, the Replace function replaces a sequence of characters with another sequence of characters. This can be useful when you’re preparing data and need to remove invalid characters from a string, performing what if analysis and need to alter a string based on inputs, etc. Let’s take our sentence we’ve been using and assume we need to change the 25th due date to the 30th. First, let’s examine the syntax to figure out how to do that.
Replace(string1, find, replacement, [start], [count], [compare])
String1 represents the string you want search. For us, this is our entire sentence. Find represents the string we are searching string1 for to perform the replace. The last required field, replacement, is the sequence of characters you want to replace string1 with. Start, an optional parameter, tells the search where to start. If you omit this, it starts at the beginning of the string. Count determines the number of replacements to make. If you leave this blank, it will perform the replace for every occurrence of find. Lastly, compare determines how to compare the strings. As before, you’ll almost always leave this to it’s default of text compare. The code for our example looks like this:
Sub ReplaceExample()
Dim stringExample As String
stringExample = "I owe you 25 dollars on September 25th"
MsgBox Replace(stringExample, "25th", "30th")
End Sub
Other Useful Functions to Edit Strings in Excel VBA
Len(text)
The Len VBA function accepts a string input and returns the number of characters in that string.
UCase(text) & LCase(text)
Both of these functions accept a string input and the UCase function will return the string converted to all upper-case characters while the LCase function returns the string with all characters converted to lower-case. This is very useful when doing string comparisons in VBA.
Str(number)
Str is a useful function that will accept a number from your code and return it as a string so you can then use string and text VBA functions on the number.
Split(expression, [delimiter], [limit], [compare])
Split is a very useful function that will split out substrings based on a delimiter. Expression is the text input. The optional parameter delimiter will default to a space delimiter but you can assign a comma, semi-colon, etc. Limit is also optional and determine the number of strings to split out. This will default to all strings if left blank. Compare, as with other formulas can be almost always be omitted from your function.
Real World Examples
The above functions, while good for explanation purposes, don’t always tell the full story since they aren’t real world examples of how to use these functions. I thought I would toss out a couple of examples of how to use VBA text functions to manipulate strings in Excel VBA with a couple of problems I’ve run into over the years.
The most common issue I run into with data sets, is that there is no universally accepted way to export full names. Almost every database stores names as first and last name fields. The issue is that many systems concatenate these fields together on export but may do it differently. For example, your time tracking system might export names in the format “FirstName LastName” while your payroll system exports names in the format “LastName, FirstName”. If you’re trying to match these names up on a weekly basis for payroll, this can get annoying quickly. Writing a macro to transform one or the other can save you a lot of time over the course of the year.
Sub LastNameFirstNameExample()
Dim lastrow As Long, i As Long
Dim sht As Worksheet
Dim str As String
Set sht = Worksheets("Name Transform Example")
lastrow = sht.UsedRange.Rows.Count
For i = 2 To lastrow
str = Cells(i, 1)
Cells(i, 2) = Mid(str, InStr(1, str, ",") + 2, Len(str) - InStr(1, str, ",") + 2) & " " & _
Left(str, InStr(1, str, ",") - 1)
Next i
End Sub
This subroutine is an example of how to turn the “LastName, FirstName” format into the “FirstName LastName” format, as seen in the gif above. First, we define the range we want to perform the conversion on and loop through those cells. While looping, we use a combination of the Mid, InStr, Len, and Left functions to reorder the names. Since all names are separate by a comma, it becomes very easy to find the splitting point between the last name and first name and, by using the Len & InStr functions, we can determine exactly how many characters we need to return. Alternatively, we could have used the Split function like this:
Sub LastNameFirstNameExampleTwo()
Dim lastrow As Long, i As Long
Dim sht As Worksheet
Dim str As String
Dim nameArray() As String
Set sht = Worksheets("Name Transform Example")
lastrow = sht.UsedRange.Rows.Count
For i = 2 To lastrow
str = Cells(i, 1)
nameArray = Split(str, ",")
Cells(i, 2) = LTrim(nameArray(1) & " " & nameArray(0))
Next i
End Sub
The nuts and bolts of this are the same, but this example uses a string array and the Split function inside the loop. You can easily split a string of delimited text into a string array and then reorder it. It’s important to remember that it will return all of the string around the split so we need to use the LTrim function in this case to remove the leading space at the beginning of our end result. Both examples are fine but I prefer the split and array example as it is a bit easier to read.
I hope you enjoyed this brief introduction to working with strings in Excel VBA. As always, the example code is available for download for all subscribers. It’s quick and easy to sign up and I won’t ever spam you with unwanted ads and emails. Let me know in the comments if you have any questions on the above.
Cheers,
R
No Comments