Get First and Last Word from a String

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:

First_Last_Word

42 comentários:

Leland USA said...

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

jppinto said...

Nice Leland, also a good option to use. Thanks for your feedback.

jppinto

Emm said...

Thank you JP and Leland. Just what I was looking for!

nici said...

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

jppinto said...

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

Leland USA said...

@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

Anonymous said...

Leland, you're a stinkin' genius.

Thanks!

Anonymous said...

Leland, very elegant solution! Just beutiful.

Anonymous said...

Thank you for this!

Tutorial Excel said...

Excellent post. I'd been looking for this formulas. The ones provided by Leland USA work just fine.

Jakob J Krummenacher said...

Why is this formula not recognizing the first space in the string - LEFT(A2,FIND(" ",A2)-1)?

Anonymous said...

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

Andrew Smith said...

Great article helped a lot!

Anonymous said...

Brilliant work everyone!

Saroj Baral said...

How can we separate following in excel if

John C. Geroge
Jack
Romio Petra


I Need to write
John C
Jack
Romio
in one column and
Gerge

Petra
in next column....

Anonymous said...

Brilliant - esp. the formula to extract the last word.

Unfortunately, I struggled a bit because in my XLS most lastnames were extracted - but for some I only got an empty string back.
Of course, it was a single mistake. These names had a space after the lastname (which you couldn't see).
So to implement a function, adding a trim() makes sense and makes it more reliable.
cheers

Anonymous said...

Leland you make it work very excellent. It is work here now on the sheet, it is very excellent. Also, my friend Tariq make it work with this way, also very excellent, he is happy with this excellent way to make it work the right way correctly now. Very elegant maybe too.

Quarta-Feira said...

Brilliant Leland!
Thank you very much

Iain Clements said...

Really useful post - saved me a lot of time - many thanks for writing this.

Anonymous said...

Thnak you very much

xoorox said...

Leland - nice elegant solution, thanks :)

Anonymous said...

I used Leland's trim(right(substitute... to strip the class word off of column names - worked like a charm! Many thanks.

Saad said...

This is Saad from Karachi, Pakistan. Thank you all especially Leland! All the stuff presented here was very helpful. Keep sharing knowledge and make the world even better. Thank you!

Anonymous said...

Hi there,

I would like to use this formula to extract the first number of a string. In my case the string is account number plus name. But when I do this and use a vlookup on the extracted number, i don't get a hit.

Does onayone have an idea how i can solve this?

Thx in advance :)

Frank

Anonymous said...

Hi, in the formula from Leland, if there is a space at the end of the cell, the last word is not captured, so instead of SUBSTITUTE(A1, I used SUBSTITUTE((TRIM(A1), and it worked fine.

Борис said...

Mqybe the easyest is:

LEFT(A1;FIND(" ";A1;1)-1)

MID(A1;FIND(" ";A1;FIND(" ";A1)+1);LEN(A1))

Борис said...
This comment has been removed by the author.
Anonymous said...

=RIGHT(J3,LEN(RIGHT(J3,LEN(J3)-FIND(" ",J3)))-FIND(" ",RIGHT(J3,LEN(J3)-FIND(" ",J3))))

Max121 said...

I want to thank Leland for his great formula on the last word in a cell and Anonymous.

If Anonymous (Frank) has not solved his problem as to why he was not getting a hit in a vlookup formula, I had the same problem.

When I entered the number in the first column I had to precede it with a single quote (') so that the number would be recognized as text, then it worked in the formulas described here.

Max121

Michael Pold said...

Great post, everybody!

Originally I needed to change "Lastname Firstname" to "Firstname Lastname".

The first two suggestions worked fine until I remembered some people have middle names.

Inspired by what I read here, I came up with this:

=IFERROR(CONCATENATE(RIGHT(A1;LEN(A1)-LEN(LEFT(A1;FIND(" ";A1))));" ";LEFT(A1;FIND(" ";A1)-1));"")

Anonymous said...

Man, I am so thankful for this!

Anonymous said...

Hello,

I have tried

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

and that gives me the entire cell for some reason.

any thoughts???

Azhar said...

Hi i do not know if this possible
in excel i have some words in a cell for example

a1= The boy is happy and he is playing the video games

I want to extract up to video
"he is playing the video"

I want to create a formula where it i put in one
cell "he" ie the first word
and "video" in another cell,ie the last word
and then excel extract the whole word in between

is this possible.

The Eye said...

Thank God for you Leland.

thanh nguyen said...

tu vi thấp, cuộc tỷ thí khả năng thắng không lớn, nhưng cũng không có

thể danh dự của vi sư rơi xuống. Phi kiếm này, dùng dẫn lực thuật cũng

có thể một phần khống chế, ta cho ngươi một khối lệnh bài, ngươi cầm rồi

đi kiếm linh các, tùy ý chọn một phi kiếm, đến lúc đó sử dụng!” Tôn Đại

Trụ nói xong ném ra một khối lệnh bài lệnh bài.

Vương Lâm nét mặt biến thành cổ quái cổ quái hé miệng nửa ngày không biết nên nói cái gì.

Nhìn ra nét mặt cổ quái của Vương L, Tôn Đại Trụ hừ một tiếng nói: “
dịch vụ kế toán trọn gói giá rẻ
eco green city
học kế toán tại hải phòng
khoá học kế toán thực hành
học kế toán tại long biên
trung tâm kế toán tại hà đông
http://nguoicodanh.net
http://chotruongyen.com
http://caubesieunhan.com
dịch vụ báo cáo tài chính

dịch vụ kế toán thuế trọn gói
eco green city nguyễn xiển

Ngươi đừng tưởng rằng đây là chuyện nhỏ, nhất định phải làm thật tốt, bằng không đến lúc đó làm vi sư mất mặt xấu hổ, trở về ta sẽ cho ngươi

một trận. Năm đó vi sư đã làm cho sư tổ nở mày nở mặt”

Vương Lâm cười khổ nói: “Đệ tử chắc chắn sẽ không để sư phụ thất vọng, sư phụ yên tâm đi!”
Chương 39: Cự phú
Tôn Đại Trụ vừa lòng gật đầu nói: “Lệnh bài kia ngươi lưu lại đi, được

tự do ra vào Dược Viên nhưng ngươi nhớ kỹ, nếu không được phép thảo dược

trong viện tử này ngươi không được hái nghe chưa”

phi long said...

Dịch vụ kế toán ACB chuyên cung cấp dich vu ke toan trọn gói uy tín chuyên nghiêp giá rẻ nhất tại HCM và các tỉnh lân cận với chi phí bỏ ra chỉ từ 500.000-1.500.000đ.
Tri ân khách hàng, ACB giảm giá lên đến 50% giá trị hợp đồng khi doanh nghiệp trở thành đối tác của chúng tôi.
Liên hệ: Dịch vụ kế toán , Dich vu ke toan .
Lầu 4, Tòa nhà Long Mã, 602 Cộng Hòa,P.13,Q.Tân Bình,HCM.
Hotline: (08) 62 97 97 97 - 0966 660 888.

Anonymous said...

How can you separate these:

John Brown Fox
Maria Theresa Durant Marconi
Joel Jason Armstrong O'Neil
Ma Socorro Fernandez Jordan

Upakarti Magento eCommerce said...

Thank you so much @jppinto and @Leland, you make life simple and go faster with the excel things. Brilliant, both work fine.

Anonymous said...

@Leland,
hi.
can you help me to arrange these names: lastname,firstname middle name to
firstname middle initial lastname

Roxas, Richard Jr. Alvarez
Chavez, Ryan James Galareta
Legaspi, Christian Arnaiz
Jimenez, Johnray Del Castillo
De Chavez, Rosalyn David
Dela Rosa, Ana Joy Ramirez
Manolo, Ma. Ana Santos
Santillan, Jeneth Umali

Result should be:

Richard A. Roxas Jr.
Ryan James G. Chavez
Christian A. Legaspi
Johnray D. Jimenez
Rosalyn D. De Chavez
Ana Joy R. Dela Rosa
Ma. Ana S. Manolo
Jeneth U. Santillan


thanks

Anonymous said...

Worked like a charm,

Thanks man,
Cheers

Ferdinand Marasigan said...

Hi,

How to extract this last sentence before the /?
Thankss.

https://www.amazon.com/8-in-1-Kitchen-Tool

answer should be: 8-in-1-Kitchen-Tool

Thanks.

aboshady said...

شركة شراء الاثاث المستعمل بالرياض
ارقام اثاث مستعمل بالرياض
محلات شراء الاثاث المستعمل