The LEFT, RIGHT and MID functions are usually used when you want to manipulate text.
LEFT: Retrieves a given number of characters from a cell, starting at the left hand side.
RIGHT: Retrieves a given number of characters from the right hand side of a cell.
MID: Retrieves a given number of characters from a cell, starting at a specific character
In the example above:
The formula in C2 is =LEFT(B2,4) which displays 4 characters from B2, starting from the left. The formula is copied down column C
The formula in D2 is =RIGHT(B2,3) which displays the 3 right-most characters from B2. The formula is copied down column D
The formula in E2 is =MID(A2,4,10) which displays 10 characters from A2, starting at the 4th character (which is the first character of the surname). The formula is copied down column D. The problem here is that the surnames do not contain the same number of characters. By selecting 10 as the third argument, Excel will display 10 characters but if there aren’t 10 characters to display, Excel displays UP TO 10 characters (I’ve made an assumption that no surname is more than 10 characters).
A better way to display the surname, given that the number of characters to display is variable, is to subtract 3 from the number of characters in the cell. Why? Because the first 3 characters in the cell are the initial, a full stop and a space.
Use the LEN function to determine how many characters are in a cell
The formula in F2 is =MID(A2,4,LEN(A2)-3) which displays a number of characters from A2, starting at the 4th character. The number of characters to display is calculated by subtracting 3 from the number of characters in A2.