EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

MID Function in Excel

By Jeevan A YJeevan A Y

Home » Excel » Blog » TEXT and String Functions in Excel » MID Function in Excel

MID Function in Excel

MID Function in Excel (Table of Contents)

  • MID in Excel
  • How to Use MID Function in Excel?

MID in Excel

The MID function works in the same way as the Right and Left functions do. It is all up to us what portion of the middle text we want to extract. For example, a cell contains FIRST, then using the Mid function there, we can select the character position from which we want to extract the text and the number of characters till we want. If we want from 2 positions to the next 3 characters, then we will be getting IRS as output.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

For Example:

MID (“Bangalore is the Capital of Karnataka”, 18, 7) in this formula MID function will return from the 18th character it will return 7 characters, i.e. “Capital”.

The MID function is also available in VBA too. We will discuss that at the end of this article.

MID Formula in Excel:

Below is the MID Formula in Excel.

MID Formula in Excel

Explanation of MID Formula in Excel:

MID formula has three compulsory parameters: i.e.text, start_num, num_chars.

  • text: From the text that you want to extract specified characters.
  • start_num: Starting position of the middle string.
  • [num_chars]: The number of characters you want to extract from the start-num.

Usually, the MID function in Excel is used alongside other text functions like SEARCH, REPLACE, LEN, FIND, LEFT, etc.

How to Use MID Function in Excel?

MID function in Excel is very simple and easy to use. Let understand the working of MID function in Excel by some MID Formula example. The MID function can be used as a worksheet function and as a VBA function.

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

Example #1

In this MID Formula in Excel example, we are extracting the substring from the given text string by using the MID formula.

Popular Course in this category
Sale
Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,392 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (16 Courses, 8+ Projects)

mid in excel Example 1-1

From the above table extract, characters from the 4th character extract 6 characters.

mid in excel Example 1-2

So the result will be :

mid in excel Example 1-3

Example #2

Using the MID function, we can extract First Name & Last Name. Look at the below example where we have full names from A2 to A15.

mid in excel Example 2-1

From the above table, extract first names for all the names.

mid in excel Example 2-2

Result is :

mid in excel Example 2-5

mid in excel Example 2-3

Part 1: This part determines the desired text that you want to extract the characters.

Part 2: This determines the starting position of the character.

Part 3: The SEARCH function looks for the first space (“ “) in the text and determines the placing of the space.

mid in excel Example 2-4

Here the SEARCH formula returns 5 for the first name that means space is the 5th character in the above text. So now MID function extracts 5 characters starting from 1st.

So the result will be :

MID Example 2-6

NOTE: Space is also considered as one character.

Example #3

Below is the flight number list, and we need to extract the characters starting from Y till you find “ – “

MID Example 3-1

Look at the above example where the letters are in bold we need to extract. It looks like one heck of a task, Isn’t it?. However, we can do this by using MID alongside SEARCH or FIND functions. One common thing is all the bold letters are starting with Y, and it has 6 characters in total.

MID Example 3-2

A SEARCH function will determine the starting character to extract, and from the starting point, we need to extract 6 characters.

So the Result is :

MID Example 3-3

Example #4

Below is the list of companies alongside their registration number. The task is to extract only the registration number from the list.

MID Example 4-1

Closely look at the above list; one common thing before the registration number is the word Acct. This will be our key point to determine the starting point. In the previous example, we have used SEARCH this time to look at the FIND function.

MID Example 4-2

MID Example 4-3

Part 1: This part determines the desired text that you want to extract the characters.

Part 2: The FIND function first looks for the word “Acct” in the text, and from there, we need to add 5 more characters to the list, and that determines the starting position of the character.

We need to add 5 that from Acct to registration number; it is 5 characters, including space.

Part 3: Number of characters we need to extract.

So the result will be :

MID Example 4-4

Example #5

Now we will look at the combination of RIGHT, LEFT & MID function in one example. We need to concatenate forward-slash (/) to separate Year, month & day.

Look at the below example where we have dates but no separators for a year, month and day.

MID Example 5.1

Now, we need to insert separators for the year, month, and day.

MID Example 5.2

Firstly RIGHT function extracts 2 characters from the right and inserts one forward-slash (/), i.e. 25/

Now, we need to insert separators for the year, month, and day.

The second part is MID function extracts 2 characters starting from the 5th character and insert one forward-slash (/)

i.e. 25/10/

The final part is LEFT function extracts 4 characters from the left-hand side and insert one forward-slash (/)

i.e. 25/10/2018

So the result will be :

MID Example 5.3

VBA Code to use MID Function

Like in Excel, we can use the MID function in VBA code also. The below code illustrates the usage of the MID function in VBA macros.

Sub MID_Function_Example
Dim Middle_String as string
Middle_String = Mid (“Excel can do wonders”, 14, 7)
Msgbox Middle_String
End Sub

If you run the above code message box will display wonders as your result.

Things to Remember

  • Number formatting is not part of a string and will not be extracted or counted.
  • This is designed to extract the characters from any side of a specified text.
  • If the user does not specify the last parameter, it will take 0 as by default.
  • Num_chars must be greater than or equal to zero. If it is a negative number, it will throw the error as #VALUE.
  • In the case of complex data sets, you need to use other text functions like SEARCH, FIND Num_chars parameter.
  • Use the MID function when you want to extract text from inside a text string, based on location and length.
  • The MID function returns empty text if the start_num is greater than the length of a string.

Recommended Articles

This has been a guide to MID in Excel. Here we discuss the MID Formula in Excel and how to use MID Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. Uses of AND Function in Excel
  2. Best Examples of COLUMN Function in Excel
  3. LOOKUP Function in Excel
  4. Amazing uses of FV Function in Excel
  5. How to Use MID Function in VBA?

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • 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

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

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*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.

Let’s Get Started

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.

EDUCBA

Download MID Function Excel Template

EDUCBA

डाउनलोड MID Function Excel Template

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

Special Offer - EXCEL ADVANCED Online COURSE Learn More