Sometimes, problems that seem so incredibly simple turn out to be much more difficult to solve than you would expect. A good example is the following simple looking exercise. Suppose that you have the following list of names and you need to determine both the first and last name. How would you proceed? You could do it through a macro but the easiest way is probably by using the “find” function.

What is the find function? It helps you find a specific character in a string. Here is what the formula looks like in excel:

FIND(find_text,within_text,start_nm)

The easiest without any doubt is the last name. We will simply find the “,” by doing this:

=FIND(“,”,A2)

Then, we will use the left characters up to that point minus 1 so:

=LEFT(A2,FIND(“,”,A2)-1)

The next step is finding the first name. We can build on what we did for the last name and will also need to find the next space following that (which will be the end of the first name). Here is what it would look like:

First name starts at:

=FIND(“,”,A2)+2

First name ends at:

=FIND(” “,A2,FIND(“,”,A2))+2

So the first name would be at:

=MID(A2,FIND(“,”,A2)+2,FIND(” “,A2,FIND(“,”,A2)+2)-FIND(“,”,A2)-2)