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

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

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.

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.

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?

Assuming a Date value is in cell E2, use the formula

=YEAR(E2)to pull out the year.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

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 }