Excel question - merging two cells

[spionfox]

I have two columns of info, one is a Christian name and the second is a surname.

Question is, how do I merge these tow columns to be just one column featuring Christian name AND surname together?

Thanks experts :icon_bigg

 

[Jeff]

I have two columns of info, one is a Christian name and the second is a surname.

Question is, how do I merge these tow columns to be just one column featuring Christian name AND surname together?

Thanks experts :icon_bigg

There's probably a better way, but this is how I do it...

Assuming your data is in A1 and B1..
In a third column, enter the formula =A1&B1 and it will show the contents of those two cells.
If you then want to have a cell containing just the name, rather than the results of the formula, copy and paste it as values.

You might need to add a space between the two names, the way I do that is by having a third cell (C1) with just a space in it, and the formula would then be =A1&C1&B1

 

[Jeff]

You might need to add a space between the two names, the way I do that is by having a third cell (C1) with just a space in it, and the formula would then be =A1&C1&B1

Ignore that bit....

You can put a space in the formula - but you have to put it in quotes.
eg =A1&" "&B1

 

[Matt_B]

The correct way is to do =CONCATENATE(A1," ",B1)

 

[bocadillo]

The correct way is to do =CONCATENATE(A1," ",B1)

Phew! Am I glad that I deleted my 'wrong' answer!?!

 

[Matt_B]

Phew! Am I glad that I deleted my 'wrong' answer!?!

What was your answer?

 

[bocadillo]

What was your answer?

Same as Jeff's. It just took me longer to formulate it.

 

[Darth Vodka]

Ignore that bit....

You can put a space in the formula - but you have to put it in quotes.
eg =A1&" "&B1

The correct way is to do =CONCATENATE(A1," ",B1)

Jeff's is hardly incorrect if it works...

and involves less letters in the formula than yours

i'd have done it Jeff's way...i never use concatenate, seems like a ballache to type it

the tricky one is going the other way round from

"John Smith" to John | Smith in two cells....

 

[Matt_B]

I believe in this instance then either way wouldn't make much difference, and you are right, Jeff isn't incorrect. However I am led to believe that as with programming, using CONCAT is much more efficient for joing a large number of strings, rather than just appending them.

To split the content above, goto Data->Text to Columns and select delimited, then space.

 

[spionfox]

Thanks Jeff and Beights - much appreciated :icon_lol:

 

[Darth Vodka]

To split the content above, goto Data->Text to Columns and select delimited, then space.

yeah but then "John Paul Gautier" messes you up ;-)

it's a nightmare when you have thousands and it's free text!

 

[Joe_Fox]

Using a mixture of text to columns and concatenate should mean that you can avoid the nightmare.

 

[Darth Vodka]

Using a mixture of text to columns and concatenate should mean that you can avoid the nightmare.

no it won't

you'll end having to manually go down the lot if it's from a free text field

but anyway, i digress...

 

[Joe_Fox]

If you had names such as John Paul Gautier and Paul Smith (in cells A1 and A2 to start with) then placed the formula =Concatenate(B1&C1) in cell D1 (and the same for row 2), and then had the formula =A1 in E1 and =D1 in F1, then did a text to columns on column A1 then you'd end up with the John in E1, Paul in E2 and Paul Gautier in F1 and Smith in F2.

If you had names that had many parts then just extend this.

 

[bocadillo]

...you'd end up with the John in E1, Paul in E2

Where would George and Ringo be?

 

[Darth Vodka]

If you had names such as John Paul Gautier and Paul Smith (in cells A1 and A2 to start with) then placed the formula =Concatenate(B1&C1) in cell D1 (and the same for row 2), and then had the formula =A1 in E1 and =D1 in F1, then did a text to columns on column A1 then you'd end up with the John in E1, Paul in E2 and Paul Gautier in F1 and Smith in F2.

If you had names that had many parts then just extend this.

depends on your data...honest

you can have

John Smith
John.Smith
Smith, John
J O Neill

it's a mare, i've had to do it before