EXCEL1: Merge contents of 2 columns

[aacod]

In Column A, from A2: A170, I have first and last names.

In column B, from B2:B170, I have addresses.

I want to merge the contents of the 2 columns, such that contents from Column B (addresses) is transferred to column A: e.g.:

:
Col A Col B

John Doe 1 Smith St., Town, State 00000.

FINAL:

John Doe
1 Smith St
Town
State 00000.

Thanks.

 

[levantkaien]

use the formula concatenate...ie =concatenate(a2,b2)

 

[aacod]

Thanks, levantkaien. The formula puts everything in a straight line. I want it as shown in the FINAL result as:

John Doe
1 Smith St
Town
State 00000.

Any ideas?

 

[pplstuff]

I'm assuming that the addresses are all varying lengths... Which could make it difficult.
A starting point might be.
c2 =a2&char(10)&b2

char(10) puts a square in the cell, when you push "wrap text" it will say
John Doe
1 Smith St., Town, State 000

To break down the the address, you might try some combination of find(), mid(), left(), and right(). I can't think of a way off the top of my head, but if I come up with something soon I'll re-post.

 

[aacod]

OK Thanks, pplstuff.

 

[rob.barnes01]

Maybe:

=CONCATENATE(A2&CHAR(10)&SUBSTITUTE(B2,", ",CHAR(10)))

 

[pplstuff]

Ah, to break down the address, use the "text-to-columns" feature! Don't know why I didn't see that before.

 

[aacod]

rob,

Thanks, formula works and puts everything in one line, but that is not what I want.


ppl.stuff,

I tried every option available under text to column, but I donot get the desired result, am I doing something else than what you have suggested!!!!!!

aacod

 

[rob.barnes01]

Select the column with the formula and turn on "Wrap Text". That should give you the result you're looking for.

 

[aacod]

rob,
Thanks, Did exactly what you mentioned, and got the result as desired.