Splitting Text Strings

One of the most useful and, sometimes, the trickiest formulas to figure out involve spitting text strings. So here are some handy formulas :

Where A1 contains Brad Pitt

=LEFT(A1,SEARCH(" ",A1)-1) = Brad

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-1)) = Pitt

=CONCATENATE(LEFT(A1,1)," ",TRIM(MID(A1,FIND(" ",A1),LEN(A1)-1))) = B Pitt



And if Brad had a middle name then there would need to be something a little more complicated :

Where A1 contains Brad Stanley Pitt

 =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) = Pitt

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)) = Stanley




And if Brad had an email that we wanted to split the details from then :

Where A1 contains Brad.Pitt@Gmail.com

=LEFT(A1,SEARCH(".",A1)-1) = Brad

=MID(A1,SEARCH(".",A1)+1,SEARCH("@",A1)-SEARCH(".",A1)-1) = Pitt

=LEFT(A1,SEARCH(".",A1)-1)&" "&MID(A1,SEARCH(".",A1)+1,SEARCH("@",A1)-SEARCH(".",A1)-1) = Brad Pitt


More quirky ways :

Where A1 contains Pitt, Brad

=LEFT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",50)),50,50)))&" "&LEFT(A1,FIND(",",A1)-1) = B Pitt