The MID Function in Excel

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.

8 thoughts on “The MID Function in Excel”

  1. Pingback: The Race to Dive from 120,000 Feet | EarthLogger.info
  2. 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.

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

  3. 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?

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

Comments are closed.