Combine the contents of two cells.

[Cut/Paste Help!!]

I want to copy the contents of a cell and insert it at the beginning of the
text of another cell: ie I want to combine the text contained in two cells
into one cell. I can do it manually but would like to use a macro as I have
a lot of cutting and inserting to do!!
I use Office 2000.

[Biff]

Hi!

Assume you want to combine cells A1:A100 with cells
B1:B100. In cell C1 enter this formula:

=A1&B1

If you want a space between values:

=A1&" "&B1

Drag copy down to C100 or just double click the fill
handle.

Now, if you no longer need the data in columns A and B
select C1:C100 and do Copy/Paste Special/Values and then
either delete columns A and B altogether or just delete
the range A1:B100.

Biff

>-----Original Message-----
>I want to copy the contents of a cell and insert it at
the beginning of the
>text of another cell: ie I want to combine the text
contained in two cells
>into one cell. I can do it manually but would like to
use a macro as I have
>a lot of cutting and inserting to do!!
>I use Office 2000.
>.
>

[HiArt]

Hi,

not sure why you need a macro.

To contatinate strings or cells use "&".

So in cell c1 you can put the following formula;

=A1&B1

If you then copy (Ctrl +C) the contents of C1,
put your cursor in cell B1 and
Ctrl + Down Arrow, this will take you to the last cell in the range,
move the cursor to to the right (back in to column C)
Ctrl + Shilft + Up Arrow
Press Enter

This should copy this formula for all the cells.

Note you can contatinate stings as well: e.g.

=A1 & " This is a test " & B1

HTH

Art

[Gord Dibben]

Is it always the same text added to multi-cells?

Are the cells contiguous or randomly located?

If randomly located, any criteria to designate which?


Gord Dibben Excel MVP

On Sun, 27 Feb 2005 22:33:02 -0800, "Cut/Paste Help!!" <Cut/Paste
Help!!@discussions.microsoft.com> wrote:

>I want to copy the contents of a cell and insert it at the beginning of the
>text of another cell: ie I want to combine the text contained in two cells
>into one cell. I can do it manually but would like to use a macro as I have
>a lot of cutting and inserting to do!!
>I use Office 2000.

[Cut/Paste Help!!]

Thanks for your input you Guys. I am sorry I did not make my question clearer.
I need to insert the contents of one cell into the text of another,
sometimes at the beginning, sometimes in the middle of the text of another
cell.

I am aware of what you have suggested Biff and thanks for your input,
however it does not help when inserting in the middle of the text.

Also, if I use this method, I find that the text formatting of the original
cell is lost. Any ideas?

"Cut/Paste Help!!" wrote:

> I want to copy the contents of a cell and insert it at the beginning of the
> text of another cell: ie I want to combine the text contained in two cells
> into one cell. I can do it manually but would like to use a macro as I have
> a lot of cutting and inserting to do!!
> I use Office 2000.

[HiArt]

OK,

it would help if we knew what the conditions were for placing the text in different parts. Anyway, it is still possible, you need to experiment with the LEFT and MID worksheet functions.

say A1 = "test" and b1 = "This is a car"

=LEFT(B1,10) = "This is a "
=MID(B1,11,9999) = "car"

So

=LEFT(B1,10) & A1 & " " & MID(B1,11,9999) = "This is a test car"
but so does...
=MID(B1,1,10) & A1 & " " & MID(B1,11,9999)

so you may be able to get what you want by changing the start and length parameters. =IF(A1 = "Start", MID(....

However, this would not copy the fomrating over. I would use the paint tool as you should only need to format the cell once.

Note you can use a bit of macro code to build up the above formula if that's they way you want to go, using VBA to set start and length parameters.

HTH

Art