ExcelBanter - Extract Surname from text

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Extract Surname from text (https://www.excelbanter.com/excel-discussion-misc-queries/248373-extract-surname-text.html)

Extract Surname from text

Can you help me write a formula to extract the surname for a text.

For example,

Text

1. Peter V Crazy
2. James Van-Cleef
3. N N M B Chan

Results

1. Crazy
2. Van-Cleef
3. Chan

Thanks.

Gary''s Student

Extract Surname from text

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,9999)
--
Gary''s Student - gsnu200908

"nc" wrote:

Can you help me write a formula to extract the surname for a text.

For example,

Text

1. Peter V Crazy
2. James Van-Cleef
3. N N M B Chan

Results

1. Crazy
2. Van-Cleef
3. Chan

Thanks.

Mike H

Extract Surname from text

Hi,

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

Mike

"nc" wrote:

Can you help me write a formula to extract the surname for a text.

For example,

Text

1. Peter V Crazy
2. James Van-Cleef
3. N N M B Chan

Results

1. Crazy
2. Van-Cleef
3. Chan

Thanks.

All times are GMT +1. The time now is 06:56 AM.