Excel Abstract Text From Cell (#Value) error

[bquek]

I am trying to abstract combination of text from a cell (A1). The cell comprise of FirstName, LastName, AccounNumber, and the Statement Date. The formula cannot retrieve name format such as FirstName intial, MiddleName initials, and LastName full (A2). Only works with First and Last names. Please assist with the formula (or shorter formula).

Cell A1
John Adam (*8989) Statement Date 12/5/2013
Sam Dean Garza (*9966) Statement Date 12/5/2013
Ben De La Rosa (*5676) Statement Date 2/5/2013

Output in Cell A2
JAdam (ok)
SDGarza (#Value)
BDLRosa (#Value)

Below is the formula:

=Left(A1,1)&Mid(A1,Find(" ",A1,1)+1,(Find(" ",A1,Find(" ",A1)+1)-Find(" ",A1,1)&IF(Mid(A1,Find(" ",A1,Find(" ",A1)+1)+1,1)="(",,(Mid(A1,Find(" ",A1,Find(" ",A1,1)+1)+1,Find(" ",A1,Find(" ",A1)+1)+1)-Find(" ",A1,find(" ",A1,1)+1)-1)&IF(Mid(A1,Find(" ",A1,Find(" ",A1,Find(" ",A1)+1)+1)+1,1)="(",,(Mid(A1,Find(" ",A1,Find(" ",A1,Find(" ",A1,1)+1)-1)+1,Find(" ",A1,Find(" ",A1,Find(" ",A1,Find(" ",A1q)+1)+1)+1)-Find(" ",A1,Find(" ",A1,Find(" ",A1,1)+1)+1)-1))))))

Thank you for your help.
Cheers,
Ben

 

[vogel997]

try this:
[TABLE="width: 475"]
<tbody>[TR]
[TD]Ben De La Rosa (*5676) Statement Date 2/5/2013[/TD]
[/TR]
[TR]
[TD]John Adam (*8989) Statement Date 12/5/2013[/TD]
[/TR]
[TR]
[TD]Sam Dean Garza (*9966) Statement Date 12/5/2013[/TD]
[/TR]
[TR]
[TD]Ben De La Rosa (*5676) Statement Date 2/5/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]BRosa[/TD]
[/TR]
[TR]
[TD]JAdam[/TD]
[/TR]
[TR]
[TD]SGarza[/TD]
[/TR]
[TR]
[TD]BRosa[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[TABLE="width: 745"]
<tbody>[TR]
[TD]=LEFT(A1,1)&RIGHT(LEFT(A1,FIND("(",A1)-2),LEN(LEFT(A1,FIND("(",A1)-2))-FIND("*",SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","*",LEN(LEFT(A1,FIND("(",A1)-2))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","")))))[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[bquek]

Is it possible to include their middle name initials due to the possibility of having similar first name initial and last name?

BDLRosa
SDGr

 

[vogel997]

this will add up to 2 middle initials:
[TABLE="width: 787"]
<tbody>[TR]
[TD]=LEFT(A1,1)&IF(LEN(LEFT(A1,FIND("(",A1)-2))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","")) > 1,MID(A1,FIND(" ",A1)+1,1),"")&IF(LEN(LEFT(A1,FIND("(",A1)-2))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","")) > 2,MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")&RIGHT(LEFT(A1,FIND("(",A1)-2),LEN(LEFT(A1,FIND("(",A1)-2))-FIND("*",SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","*",LEN(LEFT(A1,FIND("(",A1)-2))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)-2)," ","")))))[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[bquek]

Mission accomplished. Thank you very much.