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

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:

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

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 the VBA Left Function to Extract Text from the Left Side of the Cell and Display it 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 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.

Dim cell_1 As Range
Dim value_1 As Variant

Set cell_1 = Range("B5")

value_1 = Left(cell_1, 7)

MsgBox value_1

Read More: How to Extract Text Before Character in Excel

2. Use the VBA Right Function to Extract Text from the Right Side of the 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.

Dim cell_1 As Range
Dim value_1 As Variant

Set cell_1 = Range("B5")

value_1 = Right(cell_1, 4)

MsgBox value_1

Read More: How to Extract Text after a Specific Text in Excel

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. The 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.

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

Read More: How to Extract Text After First Space in Excel

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.

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.

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

Similar Readings

• How to Extract Text after Second Space in Excel
• How to Extract Text After Last Space in Excel
• How to Extract Text Between Two Commas in Excel

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.

Step 4:

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

Here, we extract only email addresses from a cell.

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

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

Extract_Email = CVErr(xlErrValue)

Download Practice Workbook

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

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 give your suggestions in the comment box.

Related Readings

• How to Extract Text after Second Comma in Excel
• How to Extract Text between Two Spaces in Excel
• How to Extract Text Between Two Characters in Excel
  
• How to Extract Text After a Character in Excel