How to Extract Certain Text from a Cell in Excel VBA (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Are you a VBA freak and in search of ways to extract certain text? Here we are to help you! In this article, you will learn 5 Excel VBA codes to extract distinct text from a cell.

VBA Code to Extract Certain Text from a Cell (At a Glance)

Function Required_Text(value_1 As String, location As Integer)
Dim array_1() As String
array_1 = VBA.Split(value_1, " ")
xCount = UBound(array_1)
If xCount < 1 Or (location - 1) > xCount Or location < 0 Then
Required_Text = ""
Else
Required_Text = array_1(location - 1)
End If
End Function

Generic Formula of this UDF:

Required_Text(value, location of a text from the left)

Table of Contents hide

Download Practice Workbook

5 VBA Codes to Extract Certain Text from a Cell in Excel

1. Use VBA Left Function to Extract Text from Left Side of Cell and Display in a Message Box

2. Use VBA Right Function to Extract Text from the Right Side of Cell and Display

3. Use VBA Mid Function to Extract Text from the Middle of an Excel Cell

4. Use a VBA Custom Function to Pick N-th Word from a Text String

5. Excerpt Substring from a Cell with VBA RegExp Object

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Extract Certain Text.xlsm

5 VBA Codes to Extract Certain Text from a Cell in Excel

Let’s introduce the dataset first. Here, we have 5 cells with some texts. We will extract a certain text from those cells using the upcoming VBA code examples.

1. Use VBA Left Function to Extract Text from Left Side of Cell and Display in a Message Box

The Left function in VBA returns a piece of text from a string from the leftmost position of the string. In this section, we will extract a text from the left side of a text string using this function.

Syntax of Left Function:

Left(String, Length)

Arguments Explanation:

String: This specifies the string from which the characters or text will be returned. If it’s Null, the code will return Null.

Length: This is an integer number that refers to the number of characters to return from the leftmost corner of the specified string. If this is greater than the entire string’s character number, then the code will return the whole string.

Now, follow the steps below.

Step 1:

First, go to the Sheet Name at the bottom of each sheet, e.g. here the sheet name is “Left Cell”.
Then press the right button of the mouse.
Click the View Code option from the Context Menu.

A VBA window will appear. Here we have to open a new module to write the code.

Step 2:

Click on the Module option from the Insert tab.

So, a new module appears now where we will write the code.

Step 3:

Copy and paste the following VBA code into this module.

Sub extract_text1()
Dim cell_1 As Range
Dim value_1 As Variant
Set cell_1 = Range("B5")
value_1 = Left(cell_1, 7)
MsgBox value_1
End Sub

In this VBA code, the Left function has two arguments.

cell_1 is the reference data.
The 2nd one is a number, e.g. here, it is 7 which indicates that the Left function will return 7 characters from the left of the reference string.

Step 4:

Now, press the F5 button to run this code. Or, press the Run Sub/UserForm button from the Run tab.

Here, we get the 7 characters from the left side.

Code Explanation:

Dim cell_1 As Range
Dim value_1 As Variant

Declaring the variable.

Set cell_1 = Range("B5")

Store a range value in the cell_1 variable

value_1 = Left(cell_1, 7)

Performs the Left operation and stores the value in the value_1 variable.

MsgBox value_1

View the result of the value_1 variable.

2. Use VBA Right Function to Extract Text from the Right Side of Cell and Display

We will apply the VBA Right function in this example. This function extracts characters from the right side of a string.

Syntax of the Right Function:

Right(string, length)

Arguments Explanation:

String- This refers to the reference data from where the chosen characters will be returned.

Length- This is a numeric expression that specifies the number of characters to be returned by the function.

Now, execute the following steps.

Step 1:

First, press Alt+F11 to enter the Excel VBA command window and insert a new module.
Now, paste the VBA code below into the module.

Sub extract_text2()
Dim cell_1 As Range
Dim value_1 As Variant
Set cell_1 = Range("B5")
value_1 = Right(cell_1, 4)
MsgBox value_1
End Sub

Here, Cell B5 is specified as set as cell_1 range variable, and the length of string to return is set as 4 inside the Right function.

Step 2:

Now, hit the F5 functional key from the keyboard to run the VBA code.

.Hence we’ve got 4 characters from the right-most side of Cell B7.

Code Explanation:

Dim cell_1 As Range
Dim value_1 As Variant

Declaring the variable.

Set cell_1 = Range("B5")

Store a range value in the cell_1 variable

value_1 = Right(cell_1, 4)

Performs the Right function operation and stores the value in the value_1 variable.

MsgBox value_1

View the result of the value_1 variable.

Similar Readings

3. Use VBA Mid Function to Extract Text from the Middle of an Excel Cell

This example will show how to get texts from the middle of any cell. To do that, we will apply the VBA Mid function here.

Syntax of the Mid Function:

=Mid(String,Start as Long,[Length])

Arguments Explanation:

String: The string from which a number of characters will be returned.

Start: The starting character of the string that’ll be returned.

Length: The number of characters that will be returned. Default is 1. This is an optional argument.

Now, carefully carry out the following steps.

Step 1:

Press Alt+F11 and enter a new VBA command module.
Write the following VBA code on the module.

Sub extract_text3()
Dim cell_1 As Range
Dim value_1 As Variant
Set cell_1 = Range("B7")
value_1 = Mid(cell_1, 7, 5)
MsgBox value_1
End Sub

Here, B7 is the reference cell set as cell_1 range variable, and the start position is 7 from the left of the reference string while 5 is the count of characters to return starting from the 7th position.

Step 2:

Run the code by pressing the F5 button.

The message box shows the 5 characters from the 7th position of the reference string.

Code Explanation:

Dim cell_1 As Range
Dim value_1 As Variant

Declaring the variable.

Set cell_1 = Range("B7")

Store a range value in the cell_1 variable

value_1 = Mid(cell_1, 7, 5)

Performs the Mid function operation and stores the value in the value_1 variable.

MsgBox value_1

View the result of the value_1 variable.

4. Use a VBA Custom Function to Pick N-th Word from a Text String

In this example, we will discuss how to extract a specific word from a cell. We will use a user-defined VBA function (UDF) for this purpose. After creating the UDF, we will apply it to a cell just like the other built-in functions.

Step 1:

Press Alt+F11 to enter a new VBA command module.
Copy and paste the following VBA code there.

Function Required_Text(value_1 As String, location As Integer)
Dim array_1() As String
array_1 = VBA.Split(value_1, " ")
xCount = UBound(array_1)
If xCount < 1 Or (location - 1) > xCount Or location < 0 Then
Required_Text = ""
Else
Required_Text = array_1(location - 1)
End If
End Function

Step 2:

Now, save the VBA code.
Then, go to Cell D5 of the dataset. Put the following formula.

=Required_Text(B5,4)

Here, the 1st argument in Cell B5 is considered as the reference string, and the 2nd argument expresses the position of text in Cell B5.

Step 3:

Now, press the Enter button.

Here, we get the 4th text from the string in Cell B5.

Code Explanation:

Function Required_Text(value_1 As String, location As Integer)

Declaring a function with arguments.

Dim array_1() As String

Declaring an array variable.

array_1 = VBA.Split(value_1, " ")

Split the value_1 variable based on space and store the values on array_1.

xCount = UBound(array_1)

This finds the largest value of array_1 and stores it on xCount.

If xCount < 1 Or (location - 1) > xCount Or location < 0 Then
    Required_Text = ""
Else
    Required_Text = array_1(location - 1)
End If

An IF condition id applied. This is a comparison between xCount and location variables.

5. Excerpt Substring from a Cell with VBA RegExp Object

In this example, we will show the use of the VBA RegEx object to extract a substring from a cell, e.g. Email addresses in this example. We will use again a VBA custom function for this purpose.

Step 1:

Hit Alt+F11 and enter the VBA command module.
Copy the following VBA code on the command module.

Function Extract_Email(text_1 As String, sequence As String, Optional instance_n As Integer = 0, Optional match_n As Boolean = True)
Dim text_1_matches() As String
Dim matches_index As Integer
On Error GoTo ErrHandl
Extract_Email = ""
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = sequence
regex.Global = True
regex.MultiLine = True
If True = match_n Then
regex.ignorecase = False
Else
regex.ignorecase = True
End If
Set matches = regex.Execute(text_1)
If 0 < matches.Count Then
If (0 = instance_n) Then
ReDim text_1_matches(matches.Count - 1, 0)
For matches_index = 0 To matches.Count - 1
text_1_matches(matches_index, 0) = matches.Item(matches_index)
Next matches_index
Extract_Email = text_1_matches
Else
Extract_Email = matches.Item(instance_n - 1)
End If
End If
Exit Function
ErrHandl:
Extract_Email = CVErr(xlErrValue)
End Function

Step 2:

Save the VBA code first.
A pattern will be used for this function as an argument.

Step 3:

Go to Cell C5 and put the following formula.

=Extract_Email(B5,$C$11)

Step 4:

Press the Enter button and drag the Fill Handle icon downwards.

Here, we extract only email addresses from a cell.

Code Explanation:

Function Extract_Email(text_1 As String, sequence As String, Optional instance_n As Integer = 0, Optional match_n As Boolean = True)

Declaring a function with arguments.

Dim text_1_matches() As String
Dim matches_index As Integer

Declaring an array variable.

On Error GoTo ErrHandl

When any error finds move to ErrHandl section.

Set regex = CreateObject("VBScript.RegExp")

Create an object and store it at regex variable.

regex.pattern = sequence
    regex.Global = True
    regex.MultiLine = True

Defines properties of regex.

If True = match_n Then
    regex.ignorecase = False
    Else
        regex.ignorecase = True
    End If

Performs an IF operation.

 Set matches = regex.Execute(text_1)

Stores value of matches.

If 0 < matches.Count Then
        If (0 = instance_n) Then
            ReDim text_1_matches(matches.Count - 1, 0)
            For matches_index = 0 To matches.Count - 1
                text_1_matches(matches_index, 0) = matches.Item(matches_index)
            Next matches_index
            Extract_Email = text_1_matches
        Else
            Extract_Email = matches.Item(instance_n - 1)
        End If
    End If

Two IF functions are applied here.

Extract_Email = CVErr(xlErrValue)

Checks the error of the given argument and stores on variable Extract_Email.

We used a pattern to get the Email address, that is :

[\w\.\-]+@[A-Za-z0-9\.\-]+\.[A-Za-z]{2,24}

Here, [\w\.\-]+ indicates the username of an email address. There may be any text, numbers, or special symbols.

@, We know this is a must-needed symbol of an email address. It separates the domain and user name.

[A-Za-z0-9\.\-]+, this is the domain name part. Numbers, texts, and some symbols are allowed here. But undercover is strictly prohibited.

\.[A-Za-z]{2,24}, this is for top-level domain. It consists of a Dot(.) . Most top-level domains consist of 3 letters.

Conclusion

In this article, we showed Excel VBA examples of how to extract certain text from a cell. We also extract numbers from a cell. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.