Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
787 views
in Technique[技术] by (71.8m points)

excel - Extract the last substring from a cell

I have names in a column. I need to split just the last names from that column into another column.

The last name is delimited by a space from the right side.

The contents in cell A2 = Alistair Stevens and I entered the formula in cell B2 (I need 'Stevens' in cell B2)

I tried using the following formulas:

=RIGHT(A2,FIND(" ",A2,1)-1)

=RIGHT(A2,FIND(" ",A2))

Both these formulas work for this cell but when I fill it down / copy and paste it for the cells below it doesn't work. I get the wrong values!!

A3 -> David Mckenzie

B3 -> Mckenzie
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This works, even when there are middle names:

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

If you want everything BUT the last name, check out this answer.

If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula.

Note that it is only by pure chance that your first formula =RIGHT(A2,FIND(" ",A2,1)-1) kind of works for Alistair Stevens. This is because "Alistair" and " Stevens" happen to contain the same number of characters (if you count the leading space in " Stevens").


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...