Skip to main content

Excel MID function

In Excel workbook, when you want to extract part of text from a cell value based on the location and length, the MID function can help you to solve this task. MID function is used to find and return a specific number of characters from the middle of given text string.


 Syntax:

The syntax for the MID function in Excel is:

=MID (text, start_num, num_chars)

 Arguments:

  • text: Required. The text string that you want to extract characters from.
  • start_num: Required. The location of the first character that you want to extract.
  • num_chars: Required. The total number of characters you want to extract.

Notes:

  • 1. If start_num is greater than the length of the original text, it will return an empty string ("").
  • 2. If start_num is less than 1, MID function returns the #VALUE! error value.
  • 3. If num_chars is negative, MID function will also return the #VALUE! error value.
  • 4. If the sum of start_num and num_chars greater than the total length of the original text string, the MID function will return the characters from start_num to the last character.

 Return:

Return the specific characters from the middle of text string.


 Examples:

Example 1: Basic uses of the MID function

For example, I want to extract 10 characters which begins at the third character from the list of text strings. Please enter this formula into a blank cell:

=MID(A2,3,10)


Example 2: Use MID function to extract first and last names from full names

We can also use the MID function to extract the first and last names from the full names, please do as follows:

Extract the first names from full names:

Please enter or copy the below formula into a blank cell where you want to output the result, and then drag the fill handle down to the cells you want to apply this formula, and all the first names have been extracted as following screenshot shown:

=MID(A2,1,SEARCH(" ",A2,1))

Extract the last names from full names:

Pull the last names from the full names, please apply the below formula:

=MID(A2,SEARCH(" ",A2),100)


Example 3: Use MID function to extract substrings between two delimiters

Sometimes, you may want to extract the text between the two dashes as below screenshot shown, to solve this task, combining the MID and FIND functions can help you.

Please copy the following formula into a blank cell:

=MID(A2, FIND("-",A2)+1, FIND("-", A2, FIND("-",A2)+1) - FIND("-",A2)-1)

And then, drag the fill handle down to fill this formula to other cells you need. And you will get the result as below screenshot shown:

Notes:

1. You can change the delimiter “-” to other separators as you need.

2. The above formula is applied to extract the text between the first and second occurrences of the specific delimiter.


 More Functions:

  • Excel SEARCH Function
  • The SEARCH function can help you to find the position of a specific character or substring from the given text string.
  • Excel REPLACE Function
  • The REPLACE function in Excel can help you to find and replace characters based on given location from text string with a new text.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations