Excel Video 157 uses MID to extract the first name from our list of patients. LEFT helped us retrieve the patients’ last names and RIGHT got the patients’ first names and middle initials. Now we’ll use MID to just get the patients’ first names. MID requires three parameters, a reference to some text, the starting postion, and the number of characters to extract. The text is easy. We’ll just reference the cell with the patient’s name. The starting position is two characters after the comma we found earlier using FIND (to account for the comma and the space between the last name and the first name).
To get the number of characters in the first name, I calculate the total number of characters in the patient’s full name using the LEN function, then subtract the number of characters in the last name using the FIND function, and then I subtract 3 more characters to account for the comma and the spaces between the names. If I start with the total number of characters and subtract the number of characters in the last name and the number of characters in commas and spaces, I’m left with the number of characters in the first name. That’s the number of characters we’ll feed to the MID function.
That’s what I wanted to show you about LEFT, RIGHT, and MID. Next time we’ll set up all three formulas to work without having to put extra columns in your data to calculate FIND and LEN.
