Extracting First/nth/Last Word From The String

In this Excel tutorial you will learn how to extract the first, the nth or the last word from the string.
Suppose you have some string in A1 cell. You want to extract some part of this string. To do this you need to use formulas below.

 

To extract the first word from the string just use this formula:=IFERROR(LEFT(A1,FIND(” “,A1)-1),””)You may also extract the first word from the string with a leading space:

=IFERROR(LEFT(A1,FIND(” “,A1)),””)

This is a formula which let you extract the nth word. You string is A1 cell and the number of occurence is in B1.

=TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A1),FIND(“^^”,SUBSTITUTE(TRIM(A1)&” “,” “,”^^”,B1))-1),” “,REPT(” “,100)),100))

And finally the last word you can extract with below Excel formula:

=IF(ISERR(FIND(” “,A1)),””,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))))

Template

You can download the Template here – Download

RELATED ARTICLESMORE FROM AUTHOR