How to find or extract specific text within Excel cell eg using surnames from cell containing complete name?

Answer 1

There are ways of doing this, but there are complications which mean it can't always work, but first let's look at how to do it. Say you have the name as follows:

Peter Colgan

What you do here is to find where the space is. Once you've located the space, then you take the text up to it and extract it and cut the rest of the cell out and extract it. To find the space, you use the Find function. So say the text is in A1, then the formula to find the space is:

=Find(" ",A1)

That will give you a number, representing the location of the space, in this case 6. Now what you can do is get the first 6 characters from the left of the cell, which can be done like this:

=left(A1,6)

That will get you Peter and the space. To do it in one go, you would put the Find formula into the Left formula, noting that there are 2 brackets at the end.

=Left(A1,Find(" ",A1))

To get from the space onwards, for the surname, you would use the MID function, which takes text from a cell starting at a set point, for a set amount of characters. Using the Find function again, it would be as follows:

=MID(A1,Find(" ",A1),10)

The 10 there will get the next 10 characters from the space onwards. To be accurate you need to know how long the text will be. You can use the LEN formula, which gets the length of the text in a cell minus where you found the space. So you could have this:

=MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1))

This will work for you, but there is one problem. If someone has a double-barrelled name, there is no way to tell the computer which is the surname. So if you applied the above to "James Patrick Doyle" then it will take the surname as starting from the first space and not the second one, giving "Patrick Doyle" as the surname and not "Doyle". So there is no 100% way of doing it.