# 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

• 11.8 KB Views: 8
• 74.9 KB Views: 2

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

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