Search for " " (space) from RIGHT

[paddy100]

Hopefully someone can help. I have a column of cells (Column D) within a worksheet with all the users taken from Active Directory. The data within the cell is in the format

Lastname, Firstname Title

I would like to separate the title and move it to another cell. The formula

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

is fine for this however we have several people with double barrelled names in the format

Lastame Lastame, Firstname Title
Lastame Lastame Lastname, Firstname Title

How can I separate the title only with these different variations of names? After days of searching I haven’t been able to find one.
Any and all help is greatly appreciated.

Paddy

 

[mikerickson]

Try
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

 

[paddy100]

mikerickson,
You're a genious!! Thanks

 

[pgc01]

Hi

Using the formula you posted:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(",",A1,1)+2))

 

[paddy100]

pgc01,
Thanks for the reply, but I was looking for one formula that would suit all variations.

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

is exactly what I needed.

 

[pgc01]

pgc01,
Thanks for the reply, but I was looking for one formula that would suit all variations.

Hi Paddy

I based the solution in your examples:

Lastname, Firstname Title
Lastame Lastame, Firstname Title
Lastame Lastame Lastname, Firstname Title