combine titles/names - how?

[Martin ©¿©¬]

Hi
I have titles & names is two columns
in column c I have
Aiken Mr R.
Aiken Mrs C.
Bell Mr D.
Bell Mrs E.
etc, etc,

In column d I have
Robert
Carol
David
Elaine
etc, etc

How do I join the title/name to get
Aiken Mr Robert etc, etc

 

[Mike H]

Hi,

In E1 and drag down

=LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1

Mike

 

[Mike H]

Another

=LEFT(TRIM(C1),LEN(TRIM(C1))-2)&D1

Mike

 

[Martin ©¿©¬]

Hi,
In E1 and drag down
=LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1

Thank you very much Mike
That did the job

 

[tech1NJ]

Minor recommendation, I would keep the formula below but will change the -2
to -0 if you really need to display the entire name in column C. The -2
removes that number of characters from the name in C1. Other than that, this
formula works great.

 

[Martin ©¿©¬]

When I use the formula =LEFT(TRIM(C1),LEN(TRIM(C1))-0)&D1 the names
come out as Mr & Mrs R.Aiken etc etc
How would I change the formula to create
Mr & Mrs R. Aiken (with a space after the period)?

 

[Mike H]

try this

=LEFT(TRIM(C1),LEN(TRIM(C1))-0)&" "&D1
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Martin ©¿©¬]

try this

=LEFT(TRIM(C1),LEN(TRIM(C1))-0)&" "&D1

Thank you Mike
That did the trick