Ask Experts Questions for FREE Help !
Ask
    nikeshtnt's Avatar
    nikeshtnt Posts: 52, Reputation: 1
    Junior Member
    		  
    #1

    Oct 5, 2008, 02:43 AM
    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
    Attached Files
  1. File Type: xls Name Separeation.xls (13.5 KB, 168 views)
    2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Oct 5, 2008, 08:13 AM

    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:
    Attached Files
  2. File Type: xls Name Separation2.xls (16.5 KB, 167 views)
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Married separate filing [ 1 Answers ]

Any one could give a lead for my tax return this year? I hold F-1 visa (file 1040NR-EZ), and I got married last December. My wife is Resident Alien (H1B), if we file tax return separately this year, According to this year's instruction book, she can not use stardard Deduction if I use...

Should I Separate from my Husband. [ 3 Answers ]

I am a mother of a 2 year old. I love him and I stay at home w/ him. My husband and I have been together 3 years.. the first year was rough, he cheated and verbally and physically abused me. After a life changing experience for my husband he seemed to changed so I gave him another chance. Well...

Separate Kittens from mum [ 1 Answers ]

Hello, my kittens are 6 weeks old, I think it is still early days to separate them from the mum cat. But when time is due, how do I do it? will the mum get depressed, will the kittens get sad?

Marital property vs. Separate in nc [ 2 Answers ]

We've been married for 4yrs. We both owned houses before marriage. I rented "mine" out and we lived in "his". Are the houses marital or separate under NC law? I've tried to research on the net but can't come to a definite conclusion. My realtor told me we split both homes. Somebody help!

View more questions Search