merge contents of two cells together

[-D-]

I'm running Excel 2000 and I'm trying to merge (combine) the contents of two
cells together.

I've tried:

=(C2 &" "&D2)

I've also tried:

=CONCATENATE(C2,D2)

It just leaves the formula in the cell as text. It doesn't combine the
contents of the cells I'm trying to combine. What am I doing incorrectly?

 

[JP]

The formula cell itself is formatted as text, hit Ctrl-1 and format as
General, then hit F2 and enter to re-input the formula.


HTH,
JP

 

[-D-]

Thank you JP...that fixed it.

How can I copy that forumla and apply to all my rows so it automaticaly
updates each record?

I used the formula =(C2 &" "&D2) on cell D2
Now, I want to apply the formula to cell D3,D4,D5, etc. But, the formula
needs to change accordingly =(C3 &" "&D3), =(C4 &" "&D4), =(C5 &" "&D5)




The formula cell itself is formatted as text, hit Ctrl-1 and format as
General, then hit F2 and enter to re-input the formula.


HTH,
JP

 

[-D-]

Nevermind, I figured it out.

Is there a way to keep the new contents but remove the formula so I can now
delete the other columns that I combined?

 

[BK]

Because your formula is "relative" not "absolute," you should be able to
copy that formula down the column and the cell references will update
accordingly.

Highlight the cell with the working formula. There will be a dark square in
the lower right corner of that cell. Mouse over that dark square until your
cursor changes. Then click and drag down the column to copy and update the
formula.

Don't forget to set the format for Column D to "general" rather than "text."

 

[-D-]

I was able to copy the formula and updated all the records, but if I try and
delete the columns that I combined it causes the new column to error because
it is reading the formula. Now that I've combined the two columns into a
new column, I want to delete the two columns that are no longer needed.
But, it references these two columns in the formula.

 

[Gord Dibben]

Copy the formula column and in place, Edit>Paste Special>Values>OK>Esc.

Now delete the original columns.


Gord Dibben MS Excel MVP

 

[JP]

Yes, see Gord's reply.


Thx,
JP

 

[BK]

Yes, you cannot delete a cell (or column) that is being referenced in a
formula.

Solution: Insert a new column. Click the column that has your combined
information and choose "copy." Choose "paste special" when you are ready to
paste the information into your new column, and select "values." The new
column will have the combined information but not the formula references.
After you paste the values into the new column, you can safely delete the
other three columns.

 

[Bill Ridgeway]

You have the cell formatted as 'Text'. Format it as 'General' and the
formula will work
<Format><Cells><General><OK>.

Regards.

Bill Ridgeway
Computer Solutions

 

[Gord Dibben]

And don't forget to F2 and ENTER to re-enter each formula after you have
re-formatted to General.


Gord Dibben MS Excel MVP