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:

## 44 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!

Brilliant work everyone!

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

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

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.

Brilliant Leland!

Thank you very much

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

Thnak you very much

Leland - nice elegant solution, thanks :)

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

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!

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

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.

Mqybe the easyest is:

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

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

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

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

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

Man, I am so thankful for this!

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

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.

Thank God for you Leland.

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”

Dịch vụ kế toánACB chuyên cung cấpdich vu ke toantrọ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.

How can you separate these:

John Brown Fox

Maria Theresa Durant Marconi

Joel Jason Armstrong O'Neil

Ma Socorro Fernandez Jordan

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

@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

Worked like a charm,

Thanks man,

Cheers

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.

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

ارقام اثاث مستعمل بالرياض

محلات شراء الاثاث المستعمل

s1288

daftar bluebet66

cbet mobile

dm.918kiss

osg77

livechat joker123

Interesting uses of text formulas. Another solution without formula is use Flash Fill, compatible with recent versions but is a rapid tip if you don't have a lot abilities with functions.

Post a Comment