EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA MID
Secondary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String

VBA MID

By Pradeep SPradeep S

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.

Syntax of VBA MID in Excel

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

Syntax VBA Mid

Watch our Demo Courses and Videos

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

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,522 ratings)

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 “RAMNATHR@OUTLOOK.COM” 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 “RAMNATHR@OUTLOOK.COM” 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("RAMNATHR@OUTLOOK.COM", 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("RAMNATHR@OUTLOOK.COM", 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("RAMNATHR@OUTLOOK.COM", 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
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more