how do I replace formula with value or cell reference with value ?

  • Thread starter Thread starter jameshanley39
  • Start date Start date
J

jameshanley39

how do I replace formula with value or cell code with value ?

excuse my terminology.
consider that just a subject title !!

I have 2 questions..
based on that concept

A)

suppose some cells have formulae like
=A4+5
=F3*2

If I copy and paste those cells, I get the formula in another cell.
How do I copy the value, paste the value. Not the formula?


(Note, i know using notepad can.. i'm wondering if excel alone can)

B)

How do I turn
=A4+F3+H7
into
=4+6+3

i.e. I want the formula in there. But not the original A4+F3+H7
formula. I want the =4+6+3 formula.

even notepad doesn't help me there.
 
A) Copy the cell. Select the target. Choose Edit/Paste Special/Values


B) Select each cell reference in the formula bar. Press F9.
 
A) Copy the cell. Select the target. Choose Edit/Paste Special/Values

B) Select each cell reference in the formula bar. Press F9.
Click to expand...


I see that selecting it all and doing F9 does the same as edit..paste
special values..

That solves the pasting the value problem.

But regarding the solution for turning
A4+B5 into 5+8
I get your solution, but it's too long winded.. e.g. if it's
A4+G6+F3+F5+D2+H5+D2 I wouldn't want to select each cell reference
in the formula bar.

thanks
 
select the cell containing =A4+G6+F3+F5+D2+H5+D2
Edit->Replace (or ctrl+H)
Find what: +
Replace with: &"+"&

The result contents in the formula bar will be like
=A4&"+"&G6&"+"&F3&"+"&F5&"+"&D2&"+"&H5&"+"&D2
 
select the cell containing =A4+G6+F3+F5+D2+H5+D2
Edit->Replace (or ctrl+H)
Find what: +
Replace with: &"+"&

The result contents in the formula bar will be like
=A4&"+"&G6&"+"&F3&"+"&F5&"+"&D2&"+"&H5&"+"&D2
Click to expand...

thanks, works gr8 !

(highlighting cells so it doesn't do the whole sheet)
 
Back
Top