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

Working out the End of the Month

There's a lot of times when it's useful to know the end of the month problematically, especially when the start date can change. Some examples :

Note: Make sure that Tools -> Analysis Toolpack is ticked.

Where A1 = 05/01/2012

=EOMONTH(A1,0) = 31/01/2012
=EOMONTH(A36,5) = 30/06/2012 (adding 5 months to the end of the month)
=EOMONTH(A38,-1) = 31/12/2011 (minus works too)