Oh deer! Santa’s elves have given him an Excel list of children to deliver presents to. But the addresses are ordered by surname, and the list of children can only be sorted by first name.
Santa needs to split the names into separate first name and surname columns. Luckily, he has a couple of tricks up his sleeve.
Let’s insert two new columns by right-clicking on B in the column labels and choosing ‘Insert’. Now, into cell B2, paste the formula
=LEFT(A2,(FIND(” “,A2))-1)
This formula searches for a space in the name, and copies all the text up to the space.
In cell C2, paste the formula
=RIGHT(A2,LEN(A2)-FIND(” “,A2))
This searches for the position of a space, subtracts it from the length of the text string and then copies all the text to the right of it.
Now for the fun bit. Select the cells containing the formulae and hover over the corner until a black plus appears, then double-click.
This will auto-fill the formulae into the rest of the list.
Finally, select the table, go to Sort & Filter under the Home tab, click on Custom Sort and sort by Column C.
Your table should now be ordered by surname, making Santa’s job a lot easier!
