move data that occupies multiple rows/columns into one row

[LizE]

I have data that looks like this:

company name title
address city state zip

each piece of data occupys it's own cell

I would like to copy the data so it looks like this
company name title address city state zip

so the data all appears in one row.

Thank you for your help!

 

[akphidelt]

Concatenate it
If the data started in A1 and went to G1 it would look like

=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1

And you can put anything between the " ", so if you wanted a comma to
separate some values then put &", ". The parenthesis add a space between the
vallues, without them you will have just 1 long line of text.

 

[LizE]

thanks - that worked just fine!!!

Concatenate it
If the data started in A1 and went to G1 it would look like

=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1

And you can put anything between the " ", so if you wanted a comma to
separate some values then put &", ". The parenthesis add a space between the
vallues, without them you will have just 1 long line of text.

 

[Gord Dibben]

You sure you wanted everything in one cell?

Will make for problems later when sorting or filtering or using for address
labels.

I would use a formula to place the 6 entries in 6 cells.

=OFFSET($A2;(COLUMN()-1)/3;MOD(COLUMN()-1;3))

Assuming data is in A2:C3, enter the formula in A1 and drag across.


Gord Dibben MS Excel MVP