Splitting out name, title and known as name

[Declamatory]

God morning folks,

I have the following table:

[TABLE="width: 923"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"] [TABLE="width: 923"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Job holder[/TD]
[TD="align: left"]Title[/TD]
[TD="align: left"]Forename[/TD]
[TD="align: left"]Surname[/TD]
[TD="align: left"]Known As[/TD]
[/TR]
[TR]
[TD="align: left"]Pickford, Mr Leighton[/TD]
[TD="align: left"]Mr [/TD]
[TD="align: left"]Leighton[/TD]
[TD="align: left"]Pickford[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Baines-Gueye, Mr Richard[/TD]
[TD="align: left"]Mr [/TD]
[TD="align: left"]Richard[/TD]
[TD="align: left"]Baines-Gueye[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Digne, Mrs Ngolo[/TD]
[TD="align: left"]Mrs [/TD]
[TD="align: left"]Ngolo[/TD]
[TD="align: left"]Digne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Southgate, Miss Nicola (Nikki)[/TD]
[TD="align: left"]Miss [/TD]
[TD="align: left"]Nicola (Nikki)[/TD]
[TD="align: left"]Southgate[/TD]
[TD="align: left"](Nikki)[/TD]
[/TR]
[TR]
[TD="align: left"]Schneiderlin, Doctor Morgan[/TD]
[TD="align: left"]Doctor [/TD]
[TD="align: left"]Morgan[/TD]
[TD="align: left"]Schneiderlin[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Keane, Mrs Gemma Louise (Gemma)[/TD]
[TD="align: left"]Mrs [/TD]
[TD="align: left"]Gemma Louise (Gemma)[/TD]
[TD="align: left"]Keane[/TD]
[TD="align: left"]Louise (Gemma)[/TD]
[/TR]
[TR]
[TD="align: left"]***Vacant***[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Claus, Mrs Joanne[/TD]
[TD="align: left"]Mrs [/TD]
[TD="align: left"]Joanne[/TD]
[TD="align: left"]Claus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Claus, Mrs Joanne[/TD]
[TD="align: left"]Mrs [/TD]
[TD="align: left"]Joanne[/TD]
[TD="align: left"]Claus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Datix-Pickford, Mr Jordan[/TD]
[TD="align: left"]Mr [/TD]
[TD="align: left"]Jordan[/TD]
[TD="align: left"]Datix-Pickford[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Datix-Mina, Mr Bernard[/TD]
[TD="align: left"]Mr [/TD]
[TD="align: left"]Bernard[/TD]
[TD="align: left"]Datix-Mina[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Richarlison, Mr Richard[/TD]
[TD="align: left"]Mr [/TD]
[TD="align: left"]Richard[/TD]
[TD="align: left"]Richarlison[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Coleman, Dr James Seamus[/TD]
[TD="align: left"]Dr [/TD]
[TD="align: left"]James Seamus[/TD]
[TD="align: left"]Coleman[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]

The columns split out the data from column A.

Formula in B2= =IFERROR(MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)),"")

Formula in C2= =IFERROR(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)),"")

Formula in D2= =IFERROR(LEFT(A2,SEARCH(" ",A2,1)-2),"")

Formula in E2 = =IFERROR(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)),"")

Is there a way I can:

1. remove the ‘known as’ name and parentheses from the forename column, without using MID in the formula
2. remove the parentheses in the ‘known as’ column leaving just the known as name?

Thanks and kind regards,

 

[Tim_Excel_]

The answer to question 1:
=IFERROR(LEFT(A2,FIND("(",A2,1)-2),"")
Note that with this formula I assume that there is no text after the 'known as' name.

The answer to question 2:
=IFERROR(SUBSTITUTE(SUBSTITUTE(E5,"(",""),")",""))

You'd want to replace the cell references with the formulas you already have.

 

[Declamatory]

Thanks for the reply Tim.

=IFERROR(SUBSTITUTE(SUBSTITUTE(E5,"(",""),")","")) works for Row 5 but for row 7 I get a result of Louise Gemma when her known name is just "Gemma".

Thanks

 

[Tim_Excel_]

=IFERROR(SUBSTITUTE(RIGHT(E5,LEN(E5)-FIND("(",E5)),")",""),"")

Tested and works, good luck!

 

[Declamatory]

Thanks Tim!

 

[Tim_Excel_]

Glad to have helped!