Tag Archives: left

excel substrings

Excel Substring and VBA Substring – Left, Right, Mid, Split etc.

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

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 / 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"

Read more here.

Get # characters from the right
=RIGHT("Hello";2) 
'Equals "lo"
Right("Hello", 2) 
'Result: "lo"

Read more here.

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"

Read more here.

Split string with delimiter
No function available in Excel
Split ( expression [,delimiter] [,limit] [,compare] )

Split("Hello World", " ")(1)
'Result: "World"

Read more here.

Substring examples

Excel Substring examples

The Left, Right and Mid functions are the basic Excel Substring functions. Below are some examples of Excel substrings using these:

Excel Substring Examples
Excel 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!"
    
Debug.Print Left(str, 5) 'Result: "Hello"
    
Debug.Print Right(str, 5) 'Result: "John!"
   
Debug.Print Mid(str, 7, 5) 'Result: "there"
    
For Each s In Split(str, " ")
   Debug.Print s
Next
'Result: "Hello", "there", "John!"

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.

Excel Substring Find examples
Excel Substring Find examples

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.

Substrings using InStr and InStrRev

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:

Get substring using Regex in Excel
Get substring using Regex in Excel

VBA String Functions reference

When obtaining substrings it is worth learning other useful VBA String Functions such as InStr. Do see my VBA String Functions reference to learn more!

VBA String Functions
VBA String Functions