One of the most important and widely used functions that we use while working with **VBA** in Excel is the **Mid function** of **VBA**. It takes a string as the input and returns a given number of characters from the middle of the string as output. Today in this article, I’ll show you how you can use the **Mid function** of **VBA** with proper examples and illustrations.

This will return **9** characters from the string **“Angela Catherine Nevills”**, starting from the **8th** character. It’s **“Catherine”**.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**Introduction to the Mid Function of VBA in Excel**

**⧭**** Function Objective:**

The **Mid** function takes a value as the input and returns a given number of characters starting from a given position of the value as the output.

The value can be a **string, number**, or even a **boolean**.

For example, if you insert **Mid(“Angela Catherine Nevills”,8,9), **it’ll return **“Catherine”.**

If you insert **Mid(12345,2,3),** you will get** 234.**

And insert **Mid(False,2,3), **you’ll get **als**.

**⧭**** Syntax:**

The Syntax of the **Mid function** of **VBA** is:

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

**⧭**** Arguments:**

Argument | Required / Optional | Explanation |
---|---|---|

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

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

Length | Optional | The number of characters that will be returned. Default is 1. |

**⧭**** Return Value:**

Returns a given number of characters from the middle of a string, starting from a given position.

**3 Examples of the Mid Function of VBA in Excel**

This time, let’s explore the **Mid function** of **VBA** in detail with a few examples.

**1. Separating a Given Number of Characters from the Middle of Some IDs Using the Mid Function of VBA in Excel**

Here we’ve got a data set with the **IDs** and **Names** of some employees of a company called Saturn Group.

Here, the **4th** to **7th **characters of each ID denotes the joining year of the respective employee.

Now we’ll develop a **User-Defined function** using the **Mid function** of **VBA** that’ll extract the joining year of each employee from the** Employee ID**.

You can use the following **VBA** code:

**⧭**** VBA Code:**

```
Function Joining_Year(ID)
Joining_Year = Mid(ID, 4, 4)
End Function
```

**Note: **This code creates a function called **Joining_Year**.

**⧭**** Output:**

Run this function in any cell of your worksheet with an ID as the argument.

Here, in cell **D4**, we’ve entered the formula:

`=Joining_Year(B4)`

It has returned the joining year of the first employee, **2021**.

Now you can drag the **Fill Handle** to get the joining years of the rest of the employees.

**⧭**** Explanation of the Code:**

- First, we declare a function called
**Joining_Year**that takes an ID as the input by the line**Function Joining_Year(ID)**. - Then we extract
**4**characters from the ID starting from position**4**, by the line**Joining_Year = Mid(ID, 4, 4).** - The line
**End Function**declares the end of the function.

**2. Extracting the Extensions from Some Email Addresses Using the Mid Function of VBA in Excel**

Now we’ve added a new column to the data set, that contains the Email addresses of the employees.

This time we’ll extract out the extensions of the Email Addresses using the **Mid** function of **VBA**.

The **VBA code** will be:

**⧭**** VBA Code:**

```
Function Extension(Email_Address)
For i = 1 To Len(Email_Address)
If Mid(Email_Address, i, 1) = "@" Then
Extension = Mid(Email_Address, i + 1, Len(Email_Address) - (i + 4))
End If
Next i
End Function
```

**Note: **This code creates a function called **Extension**.

**⧭**** Output:**

Run this function in any cell of your worksheet with an **Email Address** as the argument.

Here, in cell **E4**, we’ve entered the formula:

`=Extension(D4)`

It has returned the extension of the first email address.

Then you can drag the **Fill Handle** to extract out the extensions of all the email addresses.

**⧭**** Explanation of the Code**

- First, we declare a function called
**Extension**that takes any name as the input by the line**Function Extension(Email_Address)**. - Then we start an iteration with a
**for-loop**that checks each character of the**Email Address**to see whether it’s**@**or not by the line**If Mid(Email_Address, i, 1) = “@” Then**. - If it finds a
**@**, then it extracts out the required extension from the**Email Address**by the line**Extension = Mid(Email_Address, i + 1, Len(Email_Address) – (i + 4))**. - Finally, we declare the end of the function.

**3. Checking Some Texts to See whether They Contain a Specific Text or Not**

One of the most important usages of the **Mid function** is to see whether a text contains a specific text or not.

Let’s develop a function to check whether the **Email Addresses** contain the term **“gmail”** or not.

You can use the following **VBA** code:

**⧭**** VBA Code:**

```
Function Checking(Text1, Text2)
For i = 1 To Len(Text1)
If Mid(Text1, i, Len(Text2)) = Text2 Then
Checking = "Yes"
Exit For
Else
Checking = "No"
End If
Next i
End Function
```

**Note: **This code creates a function called **Checking**.

**⧭**** Output:**

Run this function in any cell of your worksheet with two texts as the argument.

Here, in cell **E4**, we’ve entered the formula:

`=Checking(D4,"gmail")`

It has returned **yes** because the 1st email address is a **Gmail** address.

Then you can drag the **Fill Handle** to do the same for all the Email IDs.

**⧭**** Explanation of the Code:**

- First, we declare a function called
**Checking**that takes two texts as the arguments by the line**Function Checking(Text1,Text2)**. - Then we start a for loop that checks each segment of
**Text1**starting from position**1**, to see whether it’s equal to**Text2**or not, by the line**If Mid(Text1, i, Len(Text2)) = Text2 Then**. - If it finds
**Text2**, then it returns**“Yes”**, otherwise it returns**“No”**. - Finally, we conclude the function by the line
**End Function**.

**Things to Remember**

- The
**1st**argument of the**Mid function**needn’t be a string always. It can be a**string**, a**number**, or even a**boolean**value. - But the
**2nd**and**3rd**arguments must be**numbers**. They can be**integers**or**fractions**but must be**numbers**. If they are**fractions**, then the**Mid function**will convert them to the nearest**integers**.

**Conclusion**

Using these methods, you can use the Mid function of VBA to extract a given number of characters from the middle of any string. Do you have any questions? Feel free to ask us.