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