Microsoft Excel functions banner image.

Another Excel function that you are highly unlikely to use on its own is the MID function: it loves to pair up with other functions to solve problems! You should have covered the LEFT and RIGHT functions (covered in the post Cleaning data in Excel) before tackling this function.

You should be comfortable with Excel format syntax such as yymmdd.

In this example, we are going to use the MID function to work out the birthdays of people based on their SA ID numbers. We know that the first 6 digits of the ID number represent the date of birth in yymmdd format. Basically, we need to return the 1st and 2nd digits as a number and a function to return the 3rd and 4th digits as a number (out of politeness we will ignore the year so as not to give peoples’ ages away).

We know we can easily get the first two digits using LEFT, so let us leave that for now and focus on the 3rd & 4th, and 5th & 6th digits.

LEFT, RIGHT & MID slice strings up, while CONCATENATE glues strings together!.

The syntax of the MID functions is as follows:

=MID(cell_reference, starting_from_position, number_of_characters)

In cell E5 in the screenshot below you can see that the cell_reference argument is C2, the cell containing the ID number we want to “slice up”.

Because we want the 3rd and 4th digits from the 13-digit string (the 2 digits representing the month of the date of birth), the starting_from_position argument is 3 and because we want 2 characters (again: the 3rd and 4th digits from the 13-digit string) to be returned the number_of_characters argument is 2.

The results of the MID function in Excel.The MID function in Excel.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.