Extract a Substring Using the MID Function in Excel

Extract a Substring Using the MID Function in Excel

There are several functions that can be used to extract a substring from a string of characters in Excel. Which function you use depends upon where the substring you want to extract is located.

• If the substring is on the right side of the data, use the RIGHT function to extract it.
• If the substring is on the left side of the data, use the LEFT function to extract it.
• If the substring has unwanted characters on both sides of the desired data, use the MIDfunction to extract it.

The MID Function Syntax

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the MID function is:

= MID ( Text , Start_num , Num_chars )

The MID Function's Arguments

The function's arguments tell the function what data it is to be affected, the starting position of the substring, and the length of the substring.

Text - the string containing the desired data.
This argument can be a cell reference to the location of the data in the worksheet.

Start_num - specifies the starting character from the left of the string to be kept.

Num_chars - specifies the number of characters to the right of the Start_num to be retained.

Example Using Excel's MID Function to Extract a Substring

In this example we will extract the substring file #6 from the string of characters located in cell D1 in the worksheet.

For help with this example see the image above.

1. Enter the following data into cell D1: &!*file #6!%


2. Click on cell D2 - this is where the extracted text will be located.


3. Click on the Formulas tab of the ribbon menu.


4. Choose Text from the ribbon to open the function drop down list.


5. Click on MID in the list to bring up the function's dialog box.


6. In the dialog box, click on the Text line.


7. Click on cell D1 in the spreadsheet.


8. Click on the Num_chars line.


9. Enter the number " 4 " (no quotes) on this line since the substring we want to extract starts with the fourth character.


10. Click on the Num_chars line.


11. Enter the number 7 on this line since substring we want to extract is seven characters long.


12. Click OK.


13. The extracted substring file #6 will appear in cell D2.


14. When you click on cell E1 the complete function = MID( D1, 3 ) appears in the formula bar above the worksheet.

http://spreadsheets.about.com/od/Text-Functions/ss/2011-01-31-excel-2010-truncate-text-strings-mid-function.htm?nl=1