Results 1 to 10 of 10

Thread: extract words in proper case from text

  1. #1

    extract words in proper case from text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Try this

    =PROPER(LEFT(A1,FIND(" ",A1)-1))

  3. #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

  4. #4
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    Do you need to convert the commas to semi-colons?

  5. #5
    I don't know that error. Can you post your workbook?

  6. #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
    Attached Files Attached Files

  7. #7
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    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

  8. #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))

  9. #9
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    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

  10. #10
    Thank you all! That name is easily "done by hand"

Posting Permissions

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