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:
13 comentários:
Here's an easy way to get the last word of a string
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
And here's an easy way to get the nth word of a string
=trim(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (nth-1)*LEN(A1)+1, LEN(A1)))
Nice Leland, also a good option to use. Thanks for your feedback.
jppinto
Thank you JP and Leland. Just what I was looking for!
All good - but I;m trying to go one step further.
where string length may be differernt
grab the last, second last,33rd last and forth last records.
lelands method does not work, as I need to go from right to left, not left to right
You can use this to get the second last:
=IFERROR(LEFT(TRIM(MID(A2,FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",3)),999)),FIND(" ",A2)),"")
to get the 3rd last:
=IFERROR(LEFT(TRIM(MID(A2,FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",2)),999)),FIND(" ",A2)),"")
and the 4th last:
=IFERROR(LEFT(TRIM(MID(A2,FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",1)),999)),FIND(" ",A2)),"")
jppinto
@nici, Here you go
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),nth*LEN(A1)), LEN(A1)))
If you want the second last, nth=2
If you want 33rd last, nth=33
etc
Leland, you're a stinkin' genius.
Thanks!
Leland, very elegant solution! Just beutiful.
Thank you for this!
Excellent post. I'd been looking for this formulas. The ones provided by Leland USA work just fine.
Why is this formula not recognizing the first space in the string - LEFT(A2,FIND(" ",A2)-1)?
Brilliant thanks.
Just a note if somethings not working, the REPT() function is new to Excel 2010, use the solution(s) from jppinto for 2007 and earlier, Leland USAs solution for 2010.
How do I know ? I have 2010, work, where I'm trying to do this, has 2007 :-)
Mark D
Great article helped a lot!
Post a Comment