# Thread: extract words in proper case from text

1. ## extract words in proper case from text

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  Reply With Quote

2. Try this

=PROPER(LEFT(A1,FIND(" ",A1)-1))  Reply With Quote

3. 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  Reply With Quote

4. Do you need to convert the commas to semi-colons?  Reply With Quote

5. I don't know that error. Can you post your workbook?  Reply With Quote

6. 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  Reply With Quote

7. 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  Reply With Quote

8. 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))  Reply With Quote

9. Originally Posted by Bob Phillips 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))
That is right thanx  Reply With Quote

10. Thank you all! That name is easily "done by hand"  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•