• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel VBA String Functions

You are here: Home / Excel VBA / Excel VBA String Functions
Excel VBA String Functions
March 10, 2017 by Philip Treacy

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
Find the length of a string
&
Concatenate (join) strings
Join
Create a string from an array of strings
Split
Create an array of sub-strings from a string
StrComp
Compare strings
= and <>
Compare strings for equality only
Like
Compare strings using pattern matching
UCase
Convert to UPPER CASE
LCase
Convert to lower case
StrConv
Convert to Proper Case (first letter of each word capitalised)
Left
Extract a sub-string starting from the left side of the string
Right
Extract a sub-string starting from the right side of the string
Mid
Extract a sub-string starting from the middle of the string
Instr
Find the position of a sub-string starting from the left side of the string
InstrRev
Find the position of a sub-string starting from the right side of the string
String
Create a string consisting of a specified number of characters
Space
Create a string consisting of a specified number of SPACES
Trim
Remove SPACE characters from both sides of the string
LTrim
Remove SPACE characters from the left side of the string
RTrim
Remove SPACE characters from the right side of the string
Replace
Replace a part of the string with another string
StrReverse
Reverse the string
Asc
Give the code value (ASCII integer) of the specified character, or the first character in the string
Chr
Give the character specified by the code value (ASCII integer)
Val
Return the numbers in the string starting from the left side. Stops when a non-numeric character is found

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

PatternMatches
Ethe letter E
ExcelThe string Excel
?A single character
*Zero or more characters
#Any single digit 0-9
[chars]Any single character in the list chars
[!chars]Any single character not in the list chars

    '''''''''''''''''''''''''''''
    ' 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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Here's a link to download a workbook with lots of examples of these functions.

Excel VBA String Functions

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:Power Query Unpivot
Next Post:Excel Map Charts

Reader Interactions

Comments

  1. barbara dudley

    July 27, 2021 at 10:41 pm

    This is wonderful. Do you have courses on VBA from the basic level to the more advanced levels?

    Reply
    • Philip Treacy

      July 28, 2021 at 8:46 am

      Thx Barbara, unfortunately at this time we don’t have a VBA course.

      Regards

      Phil

      Reply
  2. David

    May 5, 2017 at 1:10 am

    Thanks Phil – I appreciate the introduction.

    Reply
    • Philip Treacy

      May 5, 2017 at 8:56 am

      You’re welcome, glad you found it useful.

      Phil

      Reply
  3. Martin Nolan

    March 11, 2017 at 2:23 am

    Hi Philip,

    The newsletter on this topic says that with InstrRev, the position of the occurrence of the string is still counted from the right though. Actually, it is still counted from the left.

    Reply
    • Philip Treacy

      March 13, 2017 at 1:15 pm

      Oops, typo. Thanks Martin. Yes it should say the position of the occurrence of the string is counted from the left still.

      Cheers

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.