extract words in proper case from text

K

kiwi

New member
Joined
Sep 26, 2012
Messages
4
Reaction score
0
Points
0
Hello,
i have a large list of names, family and surnames together.
I want to extract surnames ( who are always beginning with capital letter) into new cell.
Somethimes surnames ( 1 or more) are splitted by " - ", ex. Paul-Piet.

thanks
 

Attachments

  • voorbeeld.xlsx
    8.4 KB · Views: 23
Try this

=PROPER(LEFT(A1,FIND(" ",A1)-1))
 
It doesn't work,
I' am working with dutch version 2007
Formula is translated correctly but results in error message' a value is not available for this function or formula'
any idea?
thanks
 
I don't know that error. Can you post your workbook?
 
comma's to semi-colons done, translation of error code is probably wrong...
here's the list with all of the names, I am curious
again thanks
 

Attachments

  • namelist.xlsx
    292.4 KB · Views: 17
Hi,
I tried this formula on your list. it works. Extracts only upper letter names: even there are two of them
=IF(40-SUM(1-IFERROR(MID(A2;ROW($1:$40);1)=" ";0))<=2;LEFT(A2;FIND(" ";A2;1));LEFT(A2;FIND(" ";A2;FIND(" ";A2;1)+1)))
It is an array formula, dont forget CSE
 
It seems to work for all names except KERVYN D'OUD MOOREGHEM. This array formula addresses this

=PROPER(LEFT(A7687,MIN(IF(CODE(MID(A7687,ROW(INDIRECT("1:"&LEN(A7687))),1))>96,ROW(INDIRECT("1:"&LEN(A7687)))))-2))
 
Thank you all! That name is easily "done by hand"
 
You must log in or register to reply here.
Back
Top