There are many times in Excel when you only need to extrapolate or evaluate PART of a cell. For this, the following functions will come in very handy:

LEN ()

• Used to find the length of a string/integer within a cell

Example:

The ID of students at ACME Community College all have 9 digits. A data input clerk has been inputting names by hand but the director of enrollment would like an audit performed on the IDs to make sure they are being input properly. How, would you do this?

Column E contains our formula. LEN(D2) is nine, perfectly in line with our criteria. At the bottom, however, we see one cell with a length of 8. How bad is our problem?

After a quick filter of our LEN formula, we can see that a total of 12 IDs from our original set have been input incorrectly. From here, we can dive into the specific details as to what was input incorrectly

RIGHT(x)

• Used to find the x number of characters on the right side of a cell

Example: The director of enrollment was to figure out how many students list a zip code of 90210 for their home address. However, the field for Address includes ALL of the student’s address, include street and house number.

Our RIGHT formula pulls out the zip code from the student address information.

A quick filter reveals that 16 of the students reported a zip code of 90210. Total time to figure out? 1 minute. So far, so good….

LEFT ()

Used to find the x number of characters on the left side of a cell.

Example:

You think you’re out of the weeds, but your director wants you to break down by month the enrollment patterns of the students. ACME C.C. enrolls students year round. Are there a few months out of the year that enroll more students? Let’s find out.

We’ve used the LEFT () formula to pull out the month that each student enrolls. Next, let’s create a table that counts how many times each month appears in our data.

After using a COUNTIF formula along with our LEFT formula across the data set, we can see that January was the most popular month in which to enroll.

MID(x)

• Used to extract a string the middle of a cell starting at a specific point

Example:

Your director has a list of every major that every student is enrolled in, and she wants you to break down the majors by frequency. There’s only one problem. Because of the way the data was entered, the data entry clerk also included ‘ACME’ (the school) and ‘CA’ (the campus for this data). MID to the rescue.

The beginning of our string has the name of the school, ACME. The end of our string has the campus location (CA). We need to pull out just the major from this string.

Once we’ve picked the cell (J2), for the MID formula we have to specify the starting point of the formula. Since we know that “ACME_” is the beginning of every entry (total of 5 characters), we can start our MID formula at character number 6. The last part of the mid formula is telling the formula how MANY characters to pull. Although we have a consistent beginning to our string, the length of the major name varies from student to student. ‘Philosophy’ is a different number of characters as compared to ‘English’…

Since we know that every entry ends in “_CA”, we have a consistent number of characters at the beginning and end of our string. The beginning has 5 characters. The end has 3 characters. Therefore, if we calculate the LENGTH of the string and subtract 8, we’ll end up with the perfect number of characters for our MID formula. Therefore, our final formula for cell K2 (shown in the pic above) is:

=MID(J2,6,LEN(J2)-8)

With our MID formula calculated for every student, we can calculate the total MAJOR count for each student using COUNTIF. It looks like Computer Science takes the top spot: