Strings are not easy to manipulate and often we need to create a Excel Substring or VBA Substring. Excel and VBA in fact have multiple functions that support obtaining substrings.
Excel vs VBA Substring functions
In the table below I compared the basic Excel and VBA Substring functions.
|Description||Excel Function||VBA Function|
|Get # characters from the left||
=LEFT("Hello";2) 'Equals "He"
Left("Hello", 2) 'Result: "He"
|Get # characters from the right||
=RIGHT("Hello";2) 'Equals "lo"
Right("Hello", 2) 'Result: "lo"
|Get # characters from the middle||MID(text_string, start_number, char_numbers)
=MID("Hello"; 2;3) 'Result: "ell"
|Mid(text_string, start_number, char_numbers)
Mid("Hello", 2, 3) 'Result: "ell"
|Split string with delimiter||
No function available in Excel
|Split ( expression [,delimiter] [,limit] [,compare] )
Split("Hello World", " ")(1) 'Result: "World"
Get VBA Substring example
VBA Substring examples
VBA Substring examples
Below similar examples using a VBA macro. Additionally in VBA you can use the VBA Split function.
Dim str As String, s As Variant str = "Hello there John!" 'Get first 5 characters from the Left Debug.Print Left(str, 5) 'Result: "Hello" 'Get first 5 characters from the Right Debug.Print Right(str, 5) 'Result: "John!" 'Get 5 characters starting at the 7th character Debug.Print Mid(str, 7, 5) 'Result: "there" 'Split the sentence into words separated by a SPACE and print them For Each s In Split(str, " ") Debug.Print s Next 'Results: "Hello", "there", "John!"
Excel Substrings using FIND
In some cases the substring you want to find is dependent on the placement of a certain character or another substring within your text. To extract the data you need you will need to use either the Excel Find function or the VBA InStr function. In the example below I am extracting the Year and Month using the LEFT, RIGHT and FIND functions.
What does Find do? If return the index position of a certain character or substring within a given string. Using this I can leverage the LEFT and RIGHT function to extract the data around the hyphen character - wherever it will be placed within a string.
VBA Substrings using InStr and InStrRev
VBA Substrings using Regex
By using Regular Expressions in VBA you can also split strings or get substrings based on virtually any defined pattern. In this post here I show how you can define a new User Defined Function which you can use as an Excel function to get a substring based on a Regular Expression pattern. See example below: