combine titles/names - how?
 

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
--
Martin
©¿©¬

combine titles/names - how?
 

Hi,

In E1 and drag down

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

Mike

"Martin ©¿©¬ @nohere.net" wrote:

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
--
Martin
©¿©¬

combine titles/names - how?
 

Another

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

Mike

"Mike H" wrote:

Hi,

In E1 and drag down

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

Mike

"Martin ©¿©¬ @nohere.net" wrote:

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
--
Martin
©¿©¬

combine titles/names - how?
 

On Sat, 3 Oct 2009 05:55:01 -0700, Mike H
wrote:

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

Thank you very much Mike
That did the job
--
Martin
©¿©¬

combine titles/names - how?
 

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.
--
tech1NJ


"Mike H" wrote:

Another

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

Mike

"Mike H" wrote:

Hi,

In E1 and drag down

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

Mike

"Martin ©¿©¬ @nohere.net" wrote:

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
--
Martin
©¿©¬

combine titles/names - Again
 

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)?
--
Martin
©¿©¬



On Sun, 4 Oct 2009 12:41:01 -0700, tech1NJ
wrote:

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.
--
tech1NJ


"Mike H" wrote:

Another

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

Mike

"Mike H" wrote:

Hi,

In E1 and drag down

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

Mike

"Martin ©¿©¬ @nohere.net" wrote:

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
--
Martin
©¿©¬

combine titles/names - Again
 

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 ©¿©¬ @nohere.net" wrote:

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)?
--
Martin
©¿©¬



On Sun, 4 Oct 2009 12:41:01 -0700, tech1NJ
wrote:

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.
--
tech1NJ


"Mike H" wrote:

Another

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

Mike

"Mike H" wrote:

Hi,

In E1 and drag down

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

Mike

"Martin ©¿©¬ @nohere.net" wrote:

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
--
Martin
©¿©¬

.

combine titles/names - Again
 

On Mon, 22 Mar 2010 05:25:01 -0700, Mike H
wrote:

try this

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

Thank you Mike
That did the trick
--
Martin
©¿©¬