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 before tackling this function.
You should be comfortable with Excel format syntax such as yymmdd.
In this tutorial:
Required knowledge:
1. Syntax
The syntax of the MID() functions is as follows:
=MID(cell_reference, starting_from_position, number_of_characters)
Returns a string.
2. Example
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!.
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.
3. Next steps
See the tutorial Cleaning data in Excel.
References:
- Microsoft (no date) MID function | Microsoft Support. Available at: https://support.microsoft.com/en-us/office/mid-function-2eba57be-0c05-4bdc-bf81-5ecf4421eb8a (Accessed: 17 March 2024).