Extract Surname From Name Field ?

[moonbase]

Hi,

Would anyone please be able to help me to extract surnames from a name field where the values are in the format of initials for fornames followed by the surname?

For example, column A in my spreadshet has values as below:

C Black
C R Dobbins
J Hartley
K Jones
K M Wilkins
N Armstrong

The surname extraction should look like:

Black
Dobbins
Hartley
Jones
Wilkins
Armstrong

I have been trying to get a formula to work that measures the field length then looks for the 1st space from the right in the field then uses the RIGHT function to get the surname but just cannot get it to work.

Any advice most appreciated

Thx

 

[Alan C]

How about text to columns and just delete the first initial or name...same with middle initial.

Good luck,

AC

 

[Matt Rogers]

Hello.

Try this:

Excel Workbook
	A	B
1	C Black	Black
2	C R Dobbins	Dobbins
3	J Hartley	Hartley
4	K Jones	Jones
5	K M Wilkins	Wilkins
6	N Armstrong	Armstrong
Sheet

 

[jimrward]

found this using google, so i take no credit

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

 

[Scott Huish]

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

Excel Workbook
	A	B
1	C Black	Black
2	C R Dobbins	Dobbins
3	J Hartley	Hartley
4	K Jones	Jones
5	K M Wilkins	Wilkins
6	N Armstrong	Armstrong
Sheet1