# Splitting out name, title and known as name

#### Declamatory

##### Active Member
God morning folks,

I have the following table:

 Job holder Title Forename Surname Known As Pickford, Mr Leighton Mr Leighton Pickford Baines-Gueye, Mr Richard Mr Richard Baines-Gueye Digne, Mrs Ngolo Mrs Ngolo Digne Southgate, Miss Nicola (Nikki) Miss Nicola (Nikki) Southgate (Nikki) Schneiderlin, Doctor Morgan Doctor Morgan Schneiderlin Keane, Mrs Gemma Louise (Gemma) Mrs Gemma Louise (Gemma) Keane Louise (Gemma) ***Vacant*** Claus, Mrs Joanne Mrs Joanne Claus Claus, Mrs Joanne Mrs Joanne Claus Datix-Pickford, Mr Jordan Mr Jordan Datix-Pickford Datix-Mina, Mr Bernard Mr Bernard Datix-Mina Richarlison, Mr Richard Mr Richard Richarlison Coleman, Dr James Seamus Dr James Seamus Coleman

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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,

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Tim_Excel_

##### Well-known Member
=IFERROR(LEFT(A2,FIND("(",A2,1)-2),"")
Note that with this formula I assume that there is no text after the 'known as' name.

=IFERROR(SUBSTITUTE(SUBSTITUTE(E5,"(",""),")",""))

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

Last edited:

#### Declamatory

##### Active Member

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

##### Well-known Member
=IFERROR(SUBSTITUTE(RIGHT(E5,LEN(E5)-FIND("(",E5)),")",""),"")

Tested and works, good luck!

Thanks Tim!