VBA Substring

What is Excel VBA Substring?

VBA Substring refers to a portion of a text string extracted from a larger string. VBA Substring functions allow you to manipulate and retrieve specific sections of text based on specified criteria. These functions are crucial for data manipulation, text parsing, and extracting relevant information from strings.

Let us look at an example. Suppose we have a list of product codes in cells A1 to A5. Each product code follows a pattern where the first three characters represent the category, and the subsequent characters represent the unique code.

We want to extract the unique code from each product code. It can be achieved using the code below. Once you run the macros, the unique codes are extracted using the VBA Substring Mid function, extracting characters from the 4th position of each product code.

Here’s what the output will look like:

How to Use Substring Functions in VBA?

Using VBA Substring functions involves selecting the appropriate function based on the extraction requirement:

Function	Description	Syntax	Example
Left	Extracts characters from the left side of a string.	Left(text, num_chars)	Left(“Hello World”, 5) returns “Hello”.
Right	Extracts characters from the right side of a string.	Right(text, num_chars)	Right(“Excel VBA”, 3) returns “VBA”.
Mid	Retrieves characters from the middle of a string.	Mid(text, start, num_chars)	Mid(“Substring”, 4, 5) returns “string”.
InStr	Identifies the position of a specific VBA substring within a string.	InStr(start, text, substring)	InStr(1, “Hello World”, “World”) returns 7.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some examples of implementing the various Substring functions.

#1 – Using Left Function

Suppose you have a list of names in Excel, and you want to extract the first name from each entry.

Step 1: Open the Visual Basic for Applications (VBA) editor in Excel. Press Alt + F11 to open the Editor.

Step 2: In the VBA editor, insert a new module by right-clicking on any existing module or the workbook name on the left pane > Insert > Module.

Step 3: In the new module, start by declaring a subroutine named “ExtractFirstName.”

Step 4: Here, a variable named cell of type Range is declared. The loop For Each cell In Range(“A1:A10”) iterates through each cell in the range A1:A10.

Step 5: Declare two variables, fullName and firstName, both of type String. These variables will store the full name and the extracted first name.

Step 6: Next, we assign the value of the current cell to the fullName variable.

Step 7:

The Left function in excel extracts characters from the beginning of the fullName string. InStr(fullName,” “) finds the position of the space character within the “fullName” string.

Subtracting one from this position gives the length of the first name, and the Left function extracts the corresponding characters.

Step 8: Here, we use the Offset property in VBA to move one column from the current cell to the right and assign the extracted “firstName” value to that adjacent cell.

Step 9: Finally, we end the loop and the sub-procedure. The loop iterates through each cell in the range, extracting first names and placing them in adjacent cells.

Step 10: Save the module and run the macro. Once you execute the macro, you will see that all the column A’s first names are automatically populated in column B.

Here is the full code:

Sub ExtractFirstName()
Dim cell As Range
For Each cell In Range(“A1:A10”)
Dim fullName As String
Dim firstName As String
fullName = cell.Value
firstName = Left(fullName, InStr(fullName, ” “) – 1)
cell.Offset(0, 1).Value = firstName
Next cell
End Sub

Example #2 – Get SubString from the Right

Imagine you have a list of file paths, and you want to extract the file names without the directory path:

Step 1: In the new module, create a subroutine named “ExtractFileNames.”

Step 2: Next, the cell variable is declared, and the loop iterates through cells in the range A1:A10.

Step 3: Now, declare two variables, “fullPath” and “filename,” to store the full path and extracted file name.

Step 4: Next, the current cell’s value is assigned to the fullPath variable.

Step 5: Here, we use the Right function in excel that extracts characters from the end of the fullPath string. InStrRev(fullPath, “\”) finds the position of the last backslash character in the fullPath string, and subtracting this position from the length of the fullPath gives the length of the file name.

Step 6: The extracted fileName value is assigned to an adjacent cell using the Offset property.

Step 7: Finally, the loop is closed, and the sub-procedure ends. Each cell’s value in the specified range is processed to extract and display file names.

Step 8: Save the module and run the macro. You will see that all the file names from the list of paths provided are automatically populated in column B.

Here is the full code:

Sub ExtractFileNames()
Dim cell As Range
For Each cell In Range(“A1:A10”)
Dim fullPath As String
Dim fileName As String
fullPath = cell.Value
fileName = Right(fullPath, Len(fullPath) – InStrRev(fullPath, “\”))
cell.Offset(0, 1).Value = fileName
Next cell
End Sub

Example #3 – Using Instr Function

Let’s say you have a list of email addresses and you want to extract the domain names:

Step 1: In the new module, start by creating a subroutine named “ExtractDomainNames.”

Step 2: Next, the cell variable is declared, and the loop iterates through cells in the range A1:A10.

Step 3: In this step, variables “emailAddress” and domain are declared to store the email address and extracted domain.

Step 4: Here, the current cell’s value is assigned to the “emailAddress” variable.

Step 5: Here, the “InStr” function locates the position of the “@” symbol in the “emailAddress” string. Adding 1 gives the starting position of the domain.

Step 6: Next, the Mid function in Excel extracts characters from the “emailAddress” string starting from the “domainStart” position, representing the domain name.

Step 7: The extracted domain value is assigned to an adjacent cell using the Offset property.

Step 8: The loop is closed, and the Sub procedure ends. Each cell’s value in the specified range is processed to extract and display domain names.

Step 9: Save the module and run the macro. Once you execute the macro, you will see that the domain names from the email IDs are automatically populated in column B.

Here is the full code:

Sub ExtractDomainNames()
Dim cell As Range
For Each cell In Range(“A1:A10”)
Dim emailAddress As String
Dim domain As String
emailAddress = cell.Value
domainStart = InStr(emailAddress, “@”) + 1
domain = Mid(emailAddress, domainStart)
cell.Offset(0, 1).Value = domain
Next cell
End Sub

Important Things To Note

1. Pay attention to the positions and lengths to avoid errors during substring extraction.
2. Utilize error handling to address cases where the desired VBA substring might not be present.
3. The VBA Substring Mid function extracts characters from a given position, allowing you to specify the length of the substring.
4. VBA Substring Search enables you to locate and work with specific data within a text.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Substring, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Substring. Here we learn how to use substring function in excel VBA, with examples & downloadable excel template. You can learn more from the following articles –

• VBA String Functions
• VBA Replace String
• VBA UCase