Create Excel VBA Function to Extract Text Between Characters

If you often work with complex strings in Excel, you’ll love this solution! In this article, you’ll learn how to create an Excel VBA function to extract text between characters — a custom function that finds and returns the text located between two user-defined delimiters like | and @.

We’ll guide you step-by-step from opening the VBA editor to using your new function directly in a worksheet cell.

---

🧠 What Is an Excel VBA Function to Extract Text Between Characters?

An Excel VBA function to extract text between characters is a type of User-Defined Function (UDF). It allows you to isolate specific parts of a string based on the position of two characters or symbols. It works just like any other Excel formula but with custom logic powered by VBA.

Example:

=ExtractText(A1, "|", "@")

From this:
INDIAN CO | AA06 @ ABCD INDIA LTD
To this:
AA06

---

💡 Why Use an Excel VBA Function to Extract Text Between Characters?

This function is especially helpful when you’re dealing with:

• Auto-generated strings from forms
• Product descriptions or codes
• Data exported from other software

Manually cutting and pasting is inefficient. With this VBA UDF, you can automate the task and reduce human error.

---

🛠️ How to Add the VBA Code in Excel

Here’s how you can get started with creating your Excel VBA function to extract text between characters:

1. Press Alt + F11 in Excel to open the VBA Editor.
2. In the Project window, right-click your workbook > Insert > Module.
3. Copy and paste the code below into the blank module.

---

✅ VBA Code: ExtractText Function

Function ExtractText(ByVal inputText As String, ByVal startChar As String, ByVal endChar As String) As String
    Dim startPos As Long, endPos As Long
    
    ' Find position of startChar
    startPos = InStr(inputText, startChar)
    If startPos = 0 Then
        ExtractText = "Start character not found"
        Exit Function
    End If
    
    ' Find position of endChar after startChar
    endPos = InStr(startPos + 1, inputText, endChar)
    If endPos = 0 Then
        ExtractText = "End character not found"
        Exit Function
    End If
    
    ' Extract the text between startChar and endChar
    ExtractText = Mid(inputText, startPos + Len(startChar), endPos - startPos - Len(startChar))
    ExtractText = Trim(ExtractText)
End Function

---

🔎 Examples of Excel VBA Function to Extract Text Between Characters

Input String	Formula	Output
Product [12345] is available	=ExtractText(A1, "[", "]")	12345
ID: #7890# recorded in database	=ExtractText(A1, "#", "#")	7890
Welcome (user42) to the platform	=ExtractText(A1, "(", ")")	user42

You can reuse the Excel VBA function to extract text between characters with different delimiters as needed.

---

🚨 Handling Missing Characters or Errors

If the start or end character is missing from the string:

• The function will return "Start delimiter not found" or "End delimiter not found" respectively.
• This helps you debug your data faster.

You can change the error messages in the code to suit your preferences.

---

🚀 Optional Upgrades for Power Users

Want to enhance your Excel VBA function to extract text between characters? Try:

• Making it case-sensitive by tweaking the search method.
• Returning multiple matches using a loop.
• Trimming unwanted spaces with Trim().

If you want help adding these, just comment below!

---

🎯 Conclusion: Automate Smartly with VBA

The Excel VBA function to extract text between characters is a simple but powerful tool. It eliminates manual effort, works on dynamic inputs, and boosts productivity in just a few lines of code.

Whether you’re dealing with form entries, product codes, or reports — this UDF will save time and reduce stress.