Excel Mid Function

This post will guide you how to use Excel MID function with syntax and examples in Microsoft excel.

Description

The Excel MID function returns a substring from a text string at the position that you specify.

The MID function is a build-in function in Microsoft Excel and it is categorized as a Text Function.

The MID function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MID function is as below:

= MID (text, start_num, num_chars)

Where the MID function arguments are:

  • text -This is a required argument. The text string that you want to extract substring from.
  • start_num-This is a required argument.  The position of the first character that you want to extract in text string.
  • num_chars-This is a required argument.  The number of the characters that you want to extract from a text string.

Note: 

  • If start_num is greater than the length of text, the MID function will return empty text.
  • If start_num is less than 1, the MID function will return the #VALUE! Error value.
  • If num_chars is negative, MID will return the #VALUE!  Error value.

Excel MID Function Example

The below examples will show you how to use Excel MID Text function to extract a substring from a text string.

#1 To extract 10 characters from the text string in B1, starting at the 5th character, just using the following formula:

=MID(B1,5,10)

excel mid function example1

More Excel MID Formula Examples


  • Remove Numeric Characters from a Cell
    If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Remove non numeric characters from a cell
    If you want to remove non numeric characters from a text cell in excel, you can use the array formula:{=TEXTJOIN(“”,TRUE,IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””))}
  • Get the position of Last Occurrence
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula…
  • Convet Text Date dd/mm/yy to mm/dd/yyyy as Date Format
    If you want to convert dates in the text format to the date format as mm/dd/yyyy, you can use a combination of the DATE function, the VALUE function, the RIGHT function, the MID function, and the LEFT function to create a new excel formula…
  • Split Text and Numbers
    If you want to split text and numbers, you can run the following excel formula that use the MIN function, FIND function and the LEN function within the LEFT function in excel. And if you want to extract only numbers within string, then you also need to RIGHT function to combine with the above functions..…
  • Split Multiple Lines from a Cell into Rows
    you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula….
  • Extract Attribute Values from XML/HTML Data
    If you want to extract only attribute values from xml data, you can use a combination of the MID function and the LEN function to create a new excel formula….
  • Get the position of Last Occurrence of a character or string in a cell
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula. ….
  • Get the Current Workbook Name
    If you want to get the name of the current workbook only, you can use a combination of the MID function, the CELL function and the FIND Function…
  • Get the Current Worksheet Name only
    If you want to get the current worksheet name only in excel, you can use a combination of the MID function, the CELL function and FIND function.…
  • Quickly Get Sheet Name
    If you want to quickly get current worksheet name only, then inert it into one cell. You can use a formula based on the MID function in combination with the FIND function and the Cell function……
  • Sorting IP Address
    Assuming that you have an IP list of data in the range of cells B1:B5, and you want to sort those IP list from low to high, how to achieve the result with excel formula. You need to create a formula based on the Text Function, the LEFT function, the FIND function and the MID function, the RIGHT function..…
  • Removing Salutation from Name
    To remove salutation from names string in excel, you can create a formula based on the RIGHT function, the LEN function and the FIND function……
  • Extract Part of Text String
    Assuming that you have a list of data in the range of cells B1:B5, and you want to get the most left 5 characters from cells, or you want to get the most right 4 characters as a substring from cells……
  • List all Worksheet Names
    Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
  • Insert The File Path and Filename into Cell
    If you want to insert a file path and filename into a cell in your current worksheet, you can use the CELL function to create a formual……..

 

Related Posts

How to Count Numbers Nth Digit Equals to Specific Number in Excel
count number n digit number7

This post will guide you how to count numbers where the Nth digit equal to a specific number in Excel 2013/2016 or Excel office 365. How do I count of numbers in a given range where the Nth digit is ...

How to Convert Text to Time in Excel
convert text to time7

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to ...

How to Extract Initials From a Name in Excel
extract initials from a name2

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016. Extract Initials from a Name Using a Formula Extract Initials from a ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

How to Convert mmddyy to Date in Excel
convert mmddyyy to date9

This post will guide you how to convert non-standard date formats or text to a standard date in Excel. If you have a date with mmddyy format, and you want to change it as the normal date format as mm/dd/yyyy ...

How to Convert Military Time to Standard Time in Excel
convert military time to standard5

This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel.  How do I Convert Standard time to military ...

How to Extract Text between Two Text Strings in Excel
extract text between two words1

This post will guide you how to extract text between two given text strings in Excel. How do I get text string between two words with a formula in Excel. Extract Text between Two Text Strings Assuming that you have ...

How to Remove Numbers from Text in Excel
remove numbers from text2

This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers ...

How to Split Word into Different Cells in Excel
split word into different cells7

This post will guide you how to split word into different columns with a formula in Excel. How do I split word or number into separate cells with VBA Macro code in Excel 2010/1013/2016. Split Word into Different Cells with ...

Insert Character or Text to Cells
insert text to cells6

This post will guide you how to insert character or text in middle of cells in Excel. How do I add text string or character to each cell of a column or range with a formula in Excel. How to ...

Sidebar