• 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

  • Splitting Names.xlsx
    11.8 KB · Views: 8
  • Splitting.JPG
    Splitting.JPG
    74.9 KB · Views: 2
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)))
 
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:
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.
 
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:
Back
Top