string splitting problem

[vectra]

got an excel spreadsheet which uses formulea's to extract surname; christian one and christian two from a string like
BROWN HENRY JAMES
into 3 columns
eg
BROWN
HENRY
JAMES
but if the second christian name is null (blank) the formulae's break down
any wizz out there prepared to try to solve this
<table x:str="" style="border-collapse: collapse; width: 730pt;" width="972" border="0" cellpadding="0" cellspacing="0"><col style="width: 119pt;" width="158"><col style="width: 131pt;" width="174"><col style="width: 276pt;" width="368"><col style="width: 204pt;" width="272"><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 119pt;" width="158" height="17">
</td> <td style="width: 131pt;" x:str="DEEPAK" x:fmla="=LEFT(A1,FIND(" ",A1,1)-1)" width="174">
</td> <td style="width: 276pt;" x:str="KUMAR" x:fmla="=MID(A1,LEN(B1)+2,(FIND(" ",A1,LEN(B1)+2))-(LEN(B1)+2))" width="368">
</td> <td style="width: 204pt;" x:str="BHANJA" x:fmla="=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2))" width="272">=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2))</td> </tr></table>A1 contains <table x:str="" style="border-collapse: collapse; width: 409px; height: 103px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 119pt;" width="158"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 119pt;" width="158" height="17">DEEPAK KUMAR BHANJA

B1 conatins
<table x:str="" style="border-collapse: collapse; width: 286px; height: 22px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 131pt;" width="174"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 131pt;" x:str="DEEPAK" width="174" height="17">=LEFT(A1,FIND(" ",A1,1)-1)</td> </tr></table>C1 contains
<table x:str="" style="border-collapse: collapse; width: 524px; height: 30px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 276pt;" width="368"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 276pt;" x:str="KUMAR" width="368" height="17">=MID(A1,LEN(B1)+2,(FIND(" ",A1,LEN(B1)+2))-(LEN(B1)+2))
and D1 contains
<table x:str="" style="border-collapse: collapse; width: 204pt;" width="272" border="0" cellpadding="0" cellspacing="0"><col style="width: 204pt;" width="272"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 204pt;" x:str="BHANJA" width="272" height="17">=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2))</td> </tr></table>
</td> </tr></table>
</td> </tr></table>

 

[jbeaucaire]

Change your middle name formula to:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,LEN(B1)+2,(FIND(" ",A1,LEN(B1)+2))-(LEN(B1)+2)))

The last name to:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2)),RIGHT(A1,LEN(A1)-FIND(" ",A1)))

 

[c_m]

<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 167px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>DEEPAK KUMAR BHANJA</td> <td>DEEPAK</td> <td>KUMAR</td> <td>BHANJA</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>Deepak Bhanja</td> <td>Deepak</td> <td> </td> <td>Bhanja</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>BROWN HENRY JAMES</td> <td>BROWN</td> <td>HENRY</td> <td>JAMES</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Brown James</td> <td>Brown</td> <td> </td> <td>James</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B1</td> <td>=LEFT(A1,FIND(" ",A1,1)-1)</td></tr> <tr> <td>C1</td> <td>=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,LEN(B1)+2,(FIND(" ",A1,LEN(B1)+2))-(LEN(B1)+2)))</td></tr> <tr> <td>D1</td> <td>=TRIM(RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2)+1))</td></tr></tbody></table></td></tr></tbody></table>

 

[vectra]

thanks a lot; really appreciate the prompt resolution

 

[Peter_SSs]

Some more alternatives:

Excel Workbook
	A	B	C	D
1	DEEPAK KUMAR BHANJA	DEEPAK	KUMAR	BHANJA
2	Deepak Bhanja	Deepak		Bhanja
3	BROWN HENRY JAMES	BROWN	HENRY	JAMES
4	Brown James	Brown		James
Extract Names