The MID Function in Excel

by Gregory on July 29, 2010

MID Formula in Action

By looking at my exported data you can see the Dates in column A are really just a bunch of Text. The database export format spacing is standard: Day of the Week, space, Month, space, Day, space, Time, space, Year.

I want to extract the Month and Day by using the MID Function. In cell B2 you can see the formula:

=MID(A2,5,6)

The MID function extracts characters from the middle of a text string. There are three required arguments: Text, Start_num, Num_char.

The Function Arguments Dialog Box

Insert Function Box IconClick the Insert Function (ƒx) button, next to the formula bar, to bring up the Function Argument dialog box.

As you can see in the dialog box below, the Text argument is selected and the definition is shown below. Selecting Start_num or Num_chars will also show their respective definitions.

MID Function Dialog Box

Another key feature of the Function Arguments dialog box is that the result of the formula (= “Jun 30”) is displayed below the argument boxes.

The MID Function Arguments

The Text argument is simply the reference to the text string from which you want to extract the characters. In this case, cell A2.

The Start_num argument represents the position of the first character you want to extract. I want to start at the 5th character, which is the first letter for the month abbreviation.

The Num_chars argument represents how many characters to return. I want to include the month and day, which is three letters, a space character, and two digits.

In this case, Excel recognizes “Jun 30” as a text string that represents a Date. However, since this formula does not supply a Year value, Excel assumes the current year and takes it from the computer’s System Date.

Obviously the MID Function did what was expected, but the formula needs to be expanded to include the Year in column A. The next post will review the RIGHT Function.

Related Posts Plugin for WordPress, Blogger...
Nabil Ahmed December 25, 2010 at 11:36 am

HI, i need help on this : eg. in cell A1 : “Paid to Abc Corporation ltd. via chq# 5233127 ” i want to extract the party name , only two parts of the name eg. ” Abc Corporation ” how the MID function to be used ?? I think we have to capture the data from second space to the fourth space but how ?? can anyone help me on this , i will be thankful to him / her.

Gregory December 26, 2010 at 1:46 pm

Assuming the text “Paid to Abc Corporation ltd. via chq# 5233127” is in cell A2, to extract Abc Corporation using the MID function would take the form: =MID(A2,9,15) because you want to start at the 9th character, and include 15 characters. “Paid to ” (including both space characters) is 8 characters long, so the 9th character is the second argument, and of course “Abc Corporation” is 15 characters long (including the space character).

Extracting other names of varying length would take a different formula. Assume that “Paid to ” was at the start of each cell content. Assume further that “via” follows each party name. Given those two assumptions, and with the same example text in cell A3, the following formula:

=MID(A3,9,FIND(“via”,A3)-1-9)

will extract Abc Corporation ltd. or any party name between “Paid to ” and “via”

The FIND function “finds” the starting point of “via” then subtracts one (1) for the space character in front of “via” and then subtracts the starting point to get the number of characters of the variable length party name.

Nikita October 14, 2012 at 5:19 am

I’m trying to extract just the year from a date ‘8/1/2004’. I did the =Mid(e2,3, 4) and I’m only getting the first 3 digits of the year. Am I doing this wrong or is there a better formula?

Gregory October 14, 2012 at 11:16 am

Assuming a Date value is in cell E2, use the formula =YEAR(E2) to pull out the year.

Mustaffa December 10, 2012 at 8:41 am

I have 4 rows of lines, A2:A5. In each row is a number typed at random. In column B
I would like the computer to indicate that besides the largest number is typed FOURTH, besides the lowest number is typed FIRST and the in-between numbers are typed SECOND and THIRD respectively. Can anyone help plese ?
A B
13 FIRST
17 FOURTH
14 SECOND
15 THIRD

Gregory December 10, 2012 at 8:38 pm

I believe that Chandoo did something like this on his blog. You might check there and ask this same question.

Comments on this entry are closed.

{ 2 trackbacks }

Previous post:

Next post: