EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA MID
 

VBA MID

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel VBA MID

MID Function in Excel VBA

MID Function is commonly used to extract a substring from a full-text string. It is categorized under String type variable. VBA Mid function allows you to extract the middle part of the string from a full-text string. VBA string functions do not change the original string. Usually, they will return a new string based on the function you input in the code. VBA Mid function returns a substring from within a supplied text or a string.

 

 

In VB macro window, when you click or enter CTRL + SPACE, VBA Intellisense drop-down menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor. After typing MID and click on spacebar below-mentioned syntax appears.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of VBA MID in Excel

The syntax for VBA Mid function in excel is as follows:

Syntax VBA Mid

MID(string variable, starting point, [length])
or
MID(text_string, start_number, char_numbers)
or
Mid(string_to_search, starting position, number_of_characters)

String Variable or Text string or string_to_search: It is a text string from where you want to start extracting the specified number of characters.

Start_number or Starting point: Character position or number from where to start extracting the sub-string.

Note: If you enter a greater than the number of characters in a string, then it will return an empty string (“”)

char_numbers or Length (Optional): Number of characters to extract or to return from the start position.

How to Use Excel VBA MID?

Below are the different examples to use Mid in Excel using VBA code.

You can download this VBA MID Excel Template here – VBA MID Excel Template

Excel VBA MID – Example #1

Follow the below steps to use excel VBA MID.

Step 1: Select or click on Visual Basic in the Code group in the Developer tab or you can directly click on Alt + F11 shortcut key.

VBA Mid Example 1.1

Step 2: To create a blank module, under the Microsoft excel objects, right-click on sheet 1(VB_MID) Insert and under the menu section select Module so that a new blank module gets created.

VBA Mid Module Example 1.2

Step 3: Under the Microsoft Excel objects, right-click on sheet 1 (VB_MID) Insert and under the menu section select Module so that a new blank module gets created.

Option

Excel VBA MID – Example #2

Now the blank module is created, it is also called as a code window, where you can start writing VBA MID function statement codes.

Suppose, I have the word “[email protected]” and you want to extract email domain i.e. “OUTLOOK” from this sentence with the help of VB MID function macro code.

Step 1: In the VBA editor, I have a given a name as VBA_MID_1() after typing Sub.

Code:

Sub VBA_MID_1()

End Sub

VBA Mid Example 2.1

Step 2: As the MID function is categorized under string type variables, DIM (Dimension) is used in a VBA code to declare a variable name, it’s type.

Code:

Sub VBA_MID_1()

Dim MiddleValue As String

End Sub

VBA Mid Example 2.2

Step 3: After declaring a variable, assign a value to this variable with the help of MID function.

Code:

Sub VBA_MID_1()

Dim MiddleValue As String
MiddleValue = Mid

End Sub

Example 2.3

In VB macro window, when you click or enter CTRL + SPACE, VBA Intellisense drop-down menu appears, which helps you out in writing the code & you can autocomplete words in VB editor. After typing MID press the spacebar key and the above-shown syntax appears.

Step 4: First parameter or argument is String i.e. It is a text String from where you want to start extracting the specified number of characters. Here it is “[email protected]” when using strings argument, you have to surround the text in quotation mark.

Start As Long: It is the starting position of the character from where you want to extract. Here, in this case, it is “10”

Length: It is a number of characters to extract or to return from the start position, Here, in this case, it is “7”

Code:

Sub VBA_MID_1()

Dim MiddleValue As String
MiddleValue = Mid("[email protected]", 10, 7)
MsgBox (MiddleValue)

End Sub

Example 2.5

We have completed the MID function arguments here. Now, I want to display this result of the variable in the message box. Let’s press Ctrl + Space, type Msg, in the bracket you can mention a variable name.

Step 5: Now, the code is ready, I can run it by clicking on the F5 key. Once I do that, pop up message appears, with the result in it.

VBA Mid GIF

Excel VBA MID – Example #3

Instead of output appearing in the message box, I want the result or output data to appear in the worksheet, i.e. in cell “D5”. In the worksheet, I have a data, i.e. Email id of the employee in cell “C5”, now I want to extract email domain form this, I can use the MID function with a slight modification of code.

VBA Mid Example 3.1

Let’s check out, how it can be done

Step 1: After declaring a variable, I need to input the variable name again and cell address of the full-text string with the help of Range or cell function.

Code:

Sub VBA_MID_2()

Dim MiddleValue As String
MiddleValue = Range("C5")

End Sub

VBA Mid Example 3.2

Step 2: Now, again I need to enter the variable name, and apply mid function & input its arguments.

Code:

Sub VBA_MID_2()

Dim MiddleValue As String
MiddleValue = Range("C5")
MiddleValue = Mid("[email protected]", 10, 7)

End Sub

Example 3.3

In the previous example, we entered a msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. For this, I  need to enter the range or cell function for the result to be displayed.

Step 3: Let’s apply range function, initially we need to input the range or cell function and Later enter the variable name, so that in that specific cell (“D5”), the result appears.

Code:

Sub VBA_MID_2()

Dim MiddleValue As String
MiddleValue = Range("C5")
MiddleValue = Mid("[email protected]", 10, 7)
Range("D5") = MiddleValue

End Sub

Example 3.4

Step 4: Now, the code is ready, I can run it by click on the F5 key. once I do that, you can observe an output value of MID function appearing in cell “D5”

VBA Mid 2

Things to Remember

  • In Excel VBA MID function, Length argument is an optional parameter. If you fail to enter any argument or ignore this, VBA Mid function returns all characters from the supplied start position to the end of the string.
  • In Excel VBA MID function, if the start number argument is greater than the length of the text string, then the MID function returns an empty string (zero-length).
  • The mid function is very significant and useful along with loops function, as it helps you to examine one character at a time from a string of text.

Recommended Articles

This is a guide to VBA MID. Here we discuss how to use MID in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Find and Replace
  2. VBA RGB
  3. VBA Check File Exists
  4. VBA Get Cell Value

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA MID Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW