Index:-

1- HOW TO FIND THE FIRST WORD?

2- HOW TO FIND THE LAST WORD?

May we already have the need 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 first space. For that, we will use the LEFT() function. Here’s how we can build our formula:

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

If we have Virat Kohli in one cell then Virat will be separated refer below image

 #Virat Kohli 

Because we can be dealing with different strings and some may not have a space, like the below image, No space between Virat and Kohli 

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),””)

If we use the above formula then we will get a blank cell to refer below image

This way, if we find any error on our formula, instead of showing #VALUE on the cell, we just leave is blank when this happens. In previous versions of Excel the IFERROR() function doesn’t exist 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))

HOW TO FIND THE LAST WORD?

Getting the last word from a string is not so easy because as 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(2)-FIND(“*”,SUBSTITUTE(A2,” “,”*”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))

As is the previous formula, we need to check for the 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,” “,””))))))

Thank you so much for the reading 

Please Like and share

By admin

Leave a Reply

Your email address will not be published.