Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Name Separate (https://www.askmehelpdesk.com/showthread.php?t=266352)

  • Oct 5, 2008, 02:43 AM
    nikeshtnt
    1 Attachment(s)
    Name Separate
    I have a list of names and want to get that as first name, second name and third name.

    If there is two words are there in name then it should be taken as first name and third name, second name section remains empty.

    I don't want to use Menu>Text to Column

    Can't it be done with formula?

    Sample sheet attached
  • Oct 5, 2008, 08:13 AM
    JBeaucaire
    1 Attachment(s)

    First name:
    =LEFT(B6,FIND(" ",B6))

    Middle name:
    =IF(ISERR(MID(B6,FIND(" ",B6)+1,IF(ISERR(FIND(" ",B6,FIND(" ",B6)+1)), FIND(" ",B6),FIND(" ",B6,FIND(" ",B6)+1))-FIND(" ",B6)-1)),"",MID(B6,FIND(" ",B6)+ 1,IF(ISERR(FIND(" ",B6,FIND(" ",B6)+1)),FIND(" ",B6),FIND(" ",B6,FIND(" ",B6)+1))-FIND(" ",B6)-1))

    Last name:
    =RIGHT(B6,LEN(B6)-FIND("*",SUBSTITUTE(B6," ","*",LEN(B6)-LEN(SUBSTITUTE(B6," ","")))))

    Sample attached:

  • All times are GMT -7. The time now is 07:48 AM.