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.