rename

[Martin ©¿©¬]

Hi
I have several lists of names
In C2 i have
Mr & Mrs E.
Mrs E.
Rev. G. & Mr M.
Mrs M.
Mr & Mrs J.
etc etc

in D2 I have
McAllister
Hay
Farquhar
Russell
Cuthbert
etc etc

How do I create a list in E2
McAllister Mr & Mrs E.
Hay Mrs E.
etc etc?

 

[Gord Dibben]

Assuming names are not all in C2 or D2 but rather in columns C and D

In E2 enter =D2 & " " & C2

Copy down.

If all in one cell, how are they separated?


Gord Dibben MS Excel MVP

 

[Martin ©¿©¬]

Assuming names are not all in C2 or D2 but rather in columns C and D Assumption correct

In E2 enter =D2 & " " & C2

Copy down.

This didn't work Gord
I just got =D2 & " " & C2 in every cell ?

 

[Dave Peterson]

Format the cells as General (or anything but Text).

Then reenter the top formula and drag down again.

 

[Martin ©¿©¬]

=CONCATENATE(D2,C2)

Hi Russell, this works fine
however, a space is needed in the formula

CarrickMr & Mrs F. - should be Carrick Mr & Mrs F.
How would I fix that please ?

 

[Martin ©¿©¬]

Format the cells as General (or anything but Text).

Then reenter the top formula and drag down again.

That didn't work either Dave
My colums have a header row if that makes a difference

 

[Dave Peterson]

How did it not work?

If you got the same results, then try it again. I'd guess you missed a step.

Or...

You may be looking at formulas.
Tools|Options|view tab|uncheck formulas (in xl2003 menus)

 

[Martin ©¿©¬]

How did it not work?
If you got the same results, then try it again. I'd guess you missed a step.
Or...
You may be looking at formulas.
Tools|Options|view tab|uncheck formulas (in xl2003 menus)

Did it again, several times
I still get =D2 & " " & C2 in each cell

I'm using 2007

 

[Dave Peterson]

Explain each step you've tried -- in great detail.

Did you reformat the cell to General?
Did you reenter the formula?
Did you copy the formula down using the autofill handle?

 

[Martin ©¿©¬]

Explain each step you've tried -- in great detail.
Did you reformat the cell to General? yes
Did you reenter the formula? yes
Did you copy the formula down using the autofill handle? yes

 

[Dave Peterson]

Did you check to see if you were looking at formulas?

If that's a yes, then I don't have any other suggestions.

 

[Martin ©¿©¬]

Did you check to see if you were looking at formulas?

If that's a yes, then I don't have any other suggestions.

Dave, I have just now entered =d2 & " " & c2
and it worked ?? ??""
I have no idea why
Thank you for all your help

 

[Martin ©¿©¬]

Did you check to see if you were looking at formulas?

If that's a yes, then I don't have any other suggestions.

Thank you Dave for your help

 

[Dave Peterson]

If you don't change the numberformat first, then you'll have trouble.

I'm guessing that you did the steps in the incorrect order.