Excel 2007 : copy paste special formula

**rtanner** · 12-03-2009, 03:51 PM

I need to be able to append formulas. The "copy" "paste special" "add formula" function is turning the formula from the first cell into a whole number before appending to the destination cell.

example: cell 1a contains the formula 1+1+1
cell 2a contains the formula 2+2+2

I want to move the contents of cell 1a and append them to cell 2a. Copy, paste special, add formulas, this used to result in "(1+1+1)+(2+2+2)". Now what happens is "3+(2+2+2)". Cell 1a converts to a whole number.

I need the integrity of the formulas to be visible after combining the cells.

**pike** · 12-03-2009, 04:36 PM

Hi rtanner

(2+2+2) is not really a forumlae it just 6

**JBeaucaire** · 12-03-2009, 04:39 PM

Pike, it's a formula if you put an equal sign in front.

A1 =1+1+1
A2 =2+2+2

When I copy A1, select A2 and use EDIT > PASTE SPECIAL > Add + Formulas, then I see this in A2:

=(2+2+2)+(1+1+1)

So this is working for me.

**darkyam** · 12-03-2009, 04:42 PM

You can put ' before a formula to convert it to text. If you need A1 to evaluate to a number, I would suggest a helper column with the same formulas, then do a find and replace in that column for = and replace with '=. (This assumes there is no further = sign in the formulas. If there is, try to find a point where all formulas agree that is unique, such as =IF(AND... )

My way assumes you want the cell to quite literally read the formulae. If you just want to have the basic formulae in there, use jbeaucaire's method. Note that this will make the sheet slower if you have to do this a large number of times or have a large dataset you're working with, since all the calculations will be done multiple times.

**pike** · 12-03-2009, 05:05 PM

Not a forumula in the sense =(2+2+2)
which =6
why not just have 6

**darkyam** · 12-03-2009, 05:08 PM

Methinks you're taking the OP too literally. If it's really something more like =IF(AND(A1>B1,B1>C1),D1*E1,D2*E2)+..., it could make more sense.

**JBeaucaire** · 12-03-2009, 05:20 PM

Originally Posted by pike

Not a forumula in the sense =(2+2+2)
which =6
why not just have 6

I don't think the OP wants to examine that, just examine why his formulas aren't adding themselves together when applied with the PASTE SPECIAL.

**rtanner** · 12-04-2009, 09:27 AM

Thanks guys. I unintentionally omitted the "=" sign in front of my formulas when composing my post (it is, however, in my worksheet formulas). Jbeaucaire is correct, it should work as stated =(1+1+1)+(2+2+2). Mine is not working - I thought it might be an anomoly to Excel2007, but if it's working for some of you, it should work for me.

Back to the drawing board. I'll contact our IT people. Thanks again.