• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splitting Full name into First, Middle and last name

AmitSingh

Member
Hi All,

I am applying the formula for extracting the first, middle and last name. Got the success but if only First name and last name is given then formula does not work. And if also if middle name is not present then it should display X value. Kindly help me or advice me on this. Please find the below details and formula which i have used. Also attaching the excel file named Splitting Names and screenshot for your reference.

Thank you!!
 

Attachments

AliGW

Active Member
In C7:

=IF(LEN(A7)-LEN(SUBSTITUTE(A7," ",""))>1,TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),99,99)),"X")

In D7:

=IF(LEN(A7)-LEN(SUBSTITUTE(A7," ",""))>1,TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),198,198)),TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),99,99)))
 

bosco_yip

Excel Ninja
Try,

In B7, copied across right and all copied down :

=IF((LEN($A7)-LEN(SUBSTITUTE($A7," ",""))+1=2)*(COLUMN(A$1)=2),"X",TRIM(MID(SUBSTITUTE(" "&$A7," ",REPT(" ",99)),COUNTIF($A7:A7,"<>X")*99,99)))

76714

Regards
 
Last edited:

AmitSingh

Member
In C7:

=IF(LEN(A7)-LEN(SUBSTITUTE(A7," ",""))>1,TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),99,99)),"X")

In D7:

=IF(LEN(A7)-LEN(SUBSTITUTE(A7," ",""))>1,TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),198,198)),TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",99)),99,99)))
Thank you Aligw, it works. Thanks again.
 

AmitSingh

Member
Try,

In B7, copied across right and all copied down :

=IF((LEN($A7)-LEN(SUBSTITUTE($A7," ",""))+1=2)*(COLUMN(A$1)=2),"X",TRIM(MID(SUBSTITUTE(" "&$A7," ",REPT(" ",99)),COUNTIF($A7:A7,"<>X")*99,99)))

Regards
Thank you bosco_yip for giving the solution, it helps me a lot.
 
Last edited by a moderator:

AlanSidman

Well-Known Member
Look at this link for how to do this with Flash Fill. No formula required

 
Top