Excel-User.com

Maybe you already had the needs to extract the first and last name from a string containing the complete name of a customer. I know I did! This is a good example of the application of this article.
To get the first word from a string, we need to find the first space on the string. For that we can use the FIND() function. This function works from left to right so is perfect for finding the first space on the string. Then we want to retrieve the word to the left of the fist space. For that we will use the LEFT() function. Here’s how we can build our formula:

=LEFT(A2,FIND(" ",A2)-1)

Because we can be dealing with different strings and some may not have a space, we need to check for errors on our formula so we should use the IFERROR() function on Excel 2007 and 2010 like this:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),"")

This way, if we find any error on our formula, instead of showing #VALUE on the cell, we just leave it blank when this happens. In previous versions of Excel the IFERROR() function doesn’t exists so we need to use the ISERR() function that is compatible with all Excel versions. Our formula needs to change to this:

=IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

To get the last word from a string is not so easy because what I’ve mentioned earlier, the FIND() function works from left to right so we need to find the last space on the string and get the text to the right of it. Here’s the formula:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

As in the previous formula, we need to check for errors on our formula to avoid the #VALUE error message, so our formula turns to this:

=IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)

To have a formula that is compatible to all Excel versions, we need to change our formula to this:

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

This will produce something like this example:

There are numerous occasions when we need to split a string into pieces. Depending on the type of string that we are handling we can do it using many different techniques. Here I’m going to show you one way of doing a simple string split. On column A we have the strings that we want to split. We want to get the text that is before the “,” character into column B.

So in column B we can put the following formula:

=TRIM(LEFT(A1,FIND(",",A1)-1))

Here I use 3 Excel functions: TRIM(), to remove the spaces on the beginning and on the end of the string that we get; LEFT() to get the string to the left of the “splitter”, in this case is the , character and FIND() to find the position of the “splitter” character. I remove 1 from the position that FIND() returns so that I don’t get the “,” character.

In column C we can put the following formula to the the text that is on the right of the “,” character:

=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

Here, instead of the LEFT() function, I use the RIGHT() because I want to get the text on the right of the “splitter” character. I use the LEN() function to get the number of character that the string on column A has so that I can subtract the position where the FIND() function finds the “splitter” character. This way I get the number of characters for the function RIGHT() to return, counting from the end of the string in column A. Example: the string in cell A1 has a LEN(A1) of 25 characters. The “splitter” character is found on position 9 of the string. So my formula will return on cell C1 (25-9)=16 characters counting from the right of string in cell A1, the result will be “2nd Street 2009”.