Extracting Fields from String

[ManUBlueJay]

I am working on a very large genealogy project. I need to be able to extract date, first name of person and parents name. the data will appear like this (Very consistent spaces) in A2:
January 2 Joey Joseph Schmoe (Joe & Josephine).
I would like 3 separate cells. B2:"January 2", C2:"Joey", D2: "Schmoe (Joe & Josephine)"

Thanks

 

[admiral100]

Hi,

Try this : depends of course the consistency of spaces in the sentence...

B2 =LEFT(SUBSTITUTE(A1," ","*",2),FIND("*",SUBSTITUTE(A1," ","*",2))-1)
C2 =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,3}))+1,(FIND("*",SUBSTITUTE(A1," ","*",3))-FIND("*",SUBSTITUTE(A1," ","*",2))))
D2 =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,60)


[TABLE="width: 737"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January 2 Joey Joseph Schmoe (Joe & Josephine)[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Details[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]January 2[/TD]
[TD]Joey [/TD]
[TD]Schmoe (Joe & Josephine)[/TD]
[/TR]
</tbody>[/TABLE]

 

[ManUBlueJay]

Hi,

Try this : depends of course the consistency of spaces in the sentence...

B2 =LEFT(SUBSTITUTE(A1," ","*",2),FIND("*",SUBSTITUTE(A1," ","*",2))-1)
C2 =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",{2,3}))+1,(FIND("*",SUBSTITUTE(A1," ","*",3))-FIND("*",SUBSTITUTE(A1," ","*",2))))
D2 =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,60)


[TABLE="width: 737"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January 2 Joey Joseph Schmoe (Joe & Josephine)[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Details[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]January 2[/TD]
[TD]Joey[/TD]
[TD]Schmoe (Joe & Josephine)[/TD]
[/TR]
</tbody>[/TABLE]
Works Perfect
|Thank you