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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jameshanley39@yahoo.co.uk - 01 Jun 2007 15:41 GMT

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.

Reply to this Message

JE McGimpsey - 01 Jun 2007 15:51 GMT

A) Copy the cell. Select the target. Choose Edit/Paste Special/Values

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

> how do I replace formula with value or cell code with value ?
>
[quoted text clipped - 26 lines]
>
> even notepad doesn't help me there.

Reply to this Message

jameshanley39@yahoo.co.uk - 01 Jun 2007 19:24 GMT

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

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

Reply to this Message

gr8posts - 02 Jun 2007 00:11 GMT

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

> > A) Copy the cell. Select the target. Choose Edit/Paste Special/Values
> >
[quoted text clipped - 12 lines]
>
> thanks

Reply to this Message

jameshanley39@yahoo.co.uk - 03 Jun 2007 18:14 GMT

On Jun 2, 12:11 am, gr8posts <gr8po...@discussions.microsoft.com>
wrote:

> select the cell containing =A4+G6+F3+F5+D2+H5+D2
> Edit->Replace (or ctrl+H)
[quoted text clipped - 3 lines]
> The result contents in the formula bar will be like
> =A4&"+"&G6&"+"&F3&"+"&F5&"+"&D2&"+"&H5&"+"&D2

thanks, works gr8 !

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

Reply to this Message

Rate this thread: