Excel VBA String Functions

If you are working with strings (a sequence of letters, numbers or other characters) in VBA then you are going to need to know the functions you can use to manipulate those strings.

This post looks at the VBA string functions only, not the functions you can use in the worksheet.

What's a String?

Why do I keep using the word string and not text? Because in programming, any sequence of characters stored as text is known as a string.

You can have 2016 stored as text and this is a string. So is Excel, Excel 2016, and |.. © Microsoft 2016 ..|.

The following functions work with any string, not just text characters.

String Function List

There are a lot of them so this list will help you find the one you want and see a quick explanation of what it does.

Lots of examples of these functions in use are in the workbook you can download towards the bottom of the page.

Len

I use this all the time. You'll use it in other functions when you need to provide the length of the string, like when looking for sub-strings

Length = Len(MyString)

&

This isn't a function, but you will use it all the time to join strings together.

Result = String1 & String2 & "Another String"

Join

Use Join to create one string from an array of strings. You specify a delimiter as the 2nd argument to the function.

    Dim myDays(2) As String
    
    myDays(0) = "Mon"
    myDays(1) = "Tue"
    myDays(2) = "Wed"
    
    ' Results in the string "Mon, Tue, Wed"
    Debug.Print Join(myDays, ", ")

Split

Splits a string into multiple strings, each one being stored as an element in an array. The 2nd argument to the function is the delimiter to use for splitting

    Dim myFruit() As String
    Dim Fruit As String
    
    Fruit = "Apple, Pear, Orange, Peach"
    
    ' The delimiter is optional
    ' If it is omitted, the delimiter is assumed to be a SPACE, i.e. " "
    myFruit = Split(Fruit, ", ")
    
    Debug.Print myFruit(0) 'Apple
    Debug.Print myFruit(1) 'Pear
    Debug.Print myFruit(2) 'Orange
    Debug.Print myFruit(3) 'Peach
   

StrComp

This can be quite a complicated one, but if you are just using it to check if strings are the same it's straightforward.

StrComp allows you to do a text comparison, so it's case insensitive (c = C), or a binary compare, which is case sensitive.

StrComp has the notion that a string can be of greater or less value than another. The way this works is to compare two strings character by character. The ASCII value of each character is checked, if they are the same, the next character is checked, and so on.

If the character's ASCII code in the first string is less than the code of the character in the second string, the first string is considered less than the second string, and vice versa.

The values returned by StrComp vary depending on what the result of the comparison is, you can read all about it on the MSDN reference page, but to summarise:

• If String1 is less than String2 the result is -1
• If String1 is equal to String2 the result is 0
• If String1 is greater than String2 the result is 1

 ' vbBinaryCompare is default and can be omitted
 ' vbBinaryCompare is case sensitive, vbTextCompare is not
    
    ' Both of these are equal
    Debug.Print StrComp("Excel", "Excel", vbTextCompare)
    Debug.Print StrComp("Excel", "Excel", vbBinaryCompare)

    ' The vbBinaryCompare is not equal
    Debug.Print StrComp("Excel", "excel", vbTextCompare)
    Debug.Print StrComp("Excel", "excel", vbBinaryCompare)

    ' a = 97
    ' x = 120
    ' A = 65
    ' X = 88
    
    ' a < x = -1
    Debug.Print StrComp("a", "x")
    
    ' A < x = -1
    Debug.Print StrComp("A", "x")

    ' x > a = 1
    Debug.Print StrComp("x", "a")
    
    ' x > A = 1
    Debug.Print StrComp("x", "A")

    ' abc < abd = -1
    Debug.Print StrComp("abc", "abd")
    
    ' abcde > abc = 1
    Debug.Print StrComp("abcde", "abc")
    
    ' abc < abcde = -1
    Debug.Print StrComp("abc", "abcde")

= and <>

These aren't functions either, they're operators, but they're a quick way to check if strings are equal.

    ' True
    Debug.Print "x" = "x"
    
    ' False
    Debug.Print "x" = "X"

    ' False
    Debug.Print "x" <> "x"
    
    ' True
    Debug.Print "x" <> "X"

Like

Like is another operator rather than a function, but let's just move on 🙂 . Like is very powerful but I don't see it used much. It allows you to pattern match a string. Which means you specify a predefined set of special patterns to match your string. It's very flexible and if you are used to using Regex or custom number formats, you'll get the hang of it very quickly.

Here's the MSDN reference

    '''''''''''''''''''''''''''''
    ' All of these are True
    '''''''''''''''''''''''''''''
    ' 0 or more characters
    Debug.Print "Excel" Like "*"
    
    ' Starts with E and followed by 0 or more characters
    Debug.Print "Excel" Like "E*"
    
    ' Starts with E and followed by exactly 4 characters
    Debug.Print "Excel" Like "E????"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space and then 4 single digits
    Debug.Print "Excel 2016" Like "E???? ####"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space, 3 single digits and then either 3 or 6
    Debug.Print "Excel 2013" Like "E???? ###[36]"
    Debug.Print "Excel 2016" Like "E???? ###[36]"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space, 3 single digits, then any character/digit except 0
    Debug.Print "Excel 2013" Like "E???? ###[!0]"
    Debug.Print "Excel 2016" Like "E???? ###[!0]"
    
    
    
    '''''''''''''''''''''''''
    ' These are all False
    '''''''''''''''''''''''''
    
    ' Starts with E and followed by 0 or more characters
    Debug.Print "Excel" Like "e*"
    
    ' Starts with E and followed by exactly 4 characters
    Debug.Print "Excel " Like "E????"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space and then 4 single digits
    Debug.Print "Excel 202016" Like "E???? ####"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space, 3 single digits and then either 3 or 6
    Debug.Print "Excel 2007" Like "E???? ###[36]"
    Debug.Print "Excel 2010" Like "E???? ###[36]"
    
    ' Starts with E, followed by exactly 4 characters,
    ' a space, 3 single digits, then any character/digit except 0
    Debug.Print "Excel 2010" Like "E???? ###[!036]"
    Debug.Print "Excel 2013" Like "E???? ###[!036]"
    Debug.Print "Excel 2016" Like "E???? ###[!036]"

UCase

Converts a string to upper case.

    'Prints MICROSOFT EXCEL
    Debug.Print UCase("MICROSOFT Excel")

LCase

Converts a string to lower case.

    'Prints microsoft excel
    Debug.Print LCase("MICROSOFT Excel")

StrConv

StrConv takes a 2nd argument which tells it what conversion to do. It can convert strings to upper and lower case, but is really useful as it can convert strings to proper case : where the first letter in each word is capitalized.

    'Prints MICROSOFT EXCEL
    Debug.Print StrConv("MICROSOFT Excel", vbUpperCase)

    'Prints microsoft excel
    Debug.Print StrConv("MICROSOFT Excel", vbLowerCase)

    'Prints Microsoft Excel
    Debug.Print StrConv("MICROSOFT Excel", vbProperCase)

Left

Return a sub-string starting from the left of the string. The number of characters to return is specified by the 2nd argument.

   'Prints Exc
    Debug.Print Left("Excel", 3)

Right

Return a sub-string starting from the right of the string. The number of characters to return is specified by the 2nd argument.

   'Prints cel
   Debug.Print Right("Excel", 3)

Mid

Return a sub-string starting from a starting position defined by the 2nd argument, where the number of characters to return is specified by the 3rd argument.

   'Start at character 3, and return 2 characters
   'Prints ce
   Debug.Print Mid("Excel", 3, 2)

Instr

Returns the position of the first occurrence of one string inside another.

By default this does a binary (case sensitive) comparison, but you can specify to do a text (case insensitive) comparison.

You can specify the starting position in your first string to look for the second one.

Check full syntax on MSDN

    'Prints 3
    Debug.Print InStr("Microsoft Excel", "c")

    'Start looking from character 11 onwards
    'Prints 13
    Debug.Print InStr(11, "Microsoft Excel", "c")
    
    'Prints 0 as C is not in the string
    Debug.Print InStr("Microsoft Excel", "C")
    
    ' Start required if compare type is specified
    ' We are now doing a case insensitive comaprison
    ' Prints 3
    Debug.Print InStr(1, "Microsoft Excel", "C", vbTextCompare)
    

InstrRev

Works similarly to Instr. Looks for the first occurrence of a string in another string, but starts from the right and works towards the left.

The position of the occurrence of the string is still counted from the left though.

MSDN

    ' Prints 7
    Debug.Print InStrRev("Microsoft Excel", "o")

    ' Start position 6 specified
    ' Prints 
    Debug.Print InStrRev("Microsoft Excel", "o", 6)

    ' Start argument required if compare type is specified
    ' The length of "Microsoft Excel" is 15
    ' But you'd normally use Len(MyString) to get the length of the string
    ' rather than hardcoding a number.
    
    ' Doing a case insensitive comparison the first o is at position 7
    Debug.Print InStrRev("Microsoft Excel", "O", 15, vbTextCompare)

    'But with a case sensitive comparison, there is no O so 0 is returned
    Debug.Print InStrRev("Microsoft Excel", "O", 15)

String

Create a string of specified length, of the specified character.

If you supply a string as the 2nd argument, the first character in that string is used to generate the new string.

    ' xxxxx
    Debug.Print String(5, "x")

    ' xxxxx
    Debug.Print String(5, "xo")

    ' You can also use the Chr function to specify the character
    ' Chr(65) = A
    Debug.Print String(5, Chr(65))

Space

Create a string of specified length containing only spaces.

    ' Prints a string consisting of 3 SPACE characters
    Debug.Print Space(3)

   ' You could use String and Chr(32) to do the same thing
    Debug.Print String(3, Chr(32))

Trim

Remove any SPACE characters from either end of a string. This leaves SPACES in the middle of the string.

    ' 2 x SPACES at start of string
    ' 1 x SPACE at end of string
    myString = "  Excel 2016 "
    
    'Prints "Excel 2016"
    Debug.Print Trim(myString)

LTrim

Remove any SPACE characters from the left end of the string. Leaves SPACES in the middle of the string.

    ' 2 x SPACES at start of string
    ' 1 x SPACE at end of string
    myString = "  Excel 2016 "
    
    'Prints "Excel 2016 "
    Debug.Print Trim(myString)

RTrim

Remove any SPACE characters from the right end of the string. Leaves SPACES in the middle of the string.

    ' 2 x SPACES at start of string
    ' 1 x SPACE at end of string
    myString = "  Excel 2016 "
    
    'Prints "  Excel 2016"
    Debug.Print Trim(myString)

Replace

Replace part of a string with another string, a specified number of times. If you don't specify how many occurrences of the string to replace, all occurrences are replaced.

The start position is specified by the 3rd argument, the number of occurrences to replace by the 4th. Both are optional. Comparisons can be case sensitive or case insensitive.

MSDN reference.

    ' Replace 10 with 16
    ' Prints "Excel 2016"
    Debug.Print Replace("Excel 2010", "10", "16")
    
    ' Replace 0 with 6
    ' Prints "Excel 2616"
    Debug.Print Replace("Excel 2010", "0", "6")

    ' Start at the first character
    ' and only replace the first occurrence
    ' Prints "Excel 2610"
    Debug.Print Replace("Excel 2010", "0", "6", 1, 1)

StrReverse

Reverses a string.

   ' Prints "6102 lecxE"
    Debug.Print StrReverse("Excel 2016")

Asc

Gives you the character code (ASCII value) for a character.

If you supply a string of multiple characters, it returns the value of the first character.

    ' Prints 120
    Debug.Print Asc("x")

    ' Prints 69
    Debug.Print Asc("Excel")

Chr

Gives you the character specified by the ASCII code number.

    'Prints A
    Debug.Print Chr(65)

Val

Returns the numbers on the left hand side of the string. Stops when a non-numeric character is reached.

   'Prints 0
    Debug.Print Val("Excel 2016")
    
    'Prints 2016
    Debug.Print Val("2016 Excel")
    
    'Prints 20132016
    Debug.Print Val("2013 2016 Excel")

Download the Workbook