Change a formula to an acual number

[Laura]

I want to change the formula I have created to the number it has created
Example: Cell A1 is the number 1. Cell A2 is the formula =a1+1 creatin
the number 2. I want this to be a two not a formula. Any ideas?

 

[Paul B]

Laura, copy, then paste special, valves

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Scott]

Hi Laura!

Select B1 (or all the cells you want to effect. Edit-

 

[A.W.J. Ales]

Laura,

Copy the cell and then Edit / Pate special / Values

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Frank Kabel]

Hi
not chance without using VBA. You may put the following code in your
worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
.offset(0,1).value = value+1
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This put the value of A1+1 in cell A2 everytime you change A1. But I'm
not sure why you want to do this. Why do you want to get rid of the
formula?

 

[A.W.J. Ales]

Hi Frank,

You are a bit overdoing this.
She isn't able yet to paste values and you want het to paste a macro in a
module ??? <vbg>

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Frank Kabel]

Hi Auk
thought that myself after hitting the 'Send' Button and seeing the
other replies
Always looking for the complex solution and not the simple one -
typical German habit BTW <vbg>

 

[Andy Wiggins]

1) Go to the cell
2) Press F2 (cell edit)
3) Press F9 (calculate)
4) Press "Enter"

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Bill Ridgeway]

Didn't see the original post. Hope this helps.

Highlight the cell
Click on <Edit><Copy>
Click on <Edit><Paste special><Values><OK>

Regards.

Bill Ridgeway
Computer Solutions

 

[Harald Staff]

What the $%#&...
You live and you learn.
Thanks for a new trick, Andy. Cool.

Best wishes Harald
Followup to newsgroup only please.

 

[Dave Peterson]

And if you select/highlight a portion of the formula, then the F9 will evaluate
just that portion.

Hit ctrl-Z to reverse it or hit escape to get back the original formula.

 

[A.W.J. Ales]

To add to that : If you after that step out with Enter it's actually
changed, but if you step out with escape all the changes (you can evaluate
more than one part of a formula by selecting and F9) will change back to the
original formula.

It's actually a great way to debug a formula "in parts" or for replacing
parts of the formula with values.
It's even better than the evaluating of a formula in XL XP because you can
choose the parts of the formula yourself rather than having to follow the
steps XL XP takes.

I've however never thought of using it in the way Andy did. So I do agree
with Harald.

It is however restricted in use as you can't do this for a whole range of
cells as you can with Copy / Paste special.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[fwilson]

Apologies if I've posted this in the wrong place but I'm hoping someon
can help. I have got salary data in Excel which I wish to total s
that I can find out the averages etc. I've had to clean it up as it'
from an online questionnaire so respondents have entered dat
differently (e.g. 13k, 13,000, £13,000 etc)

Trouble is all the cells have ' in front of the numbers, meaning that
can't sum them. There are 2 lists of at least 500 numbers... and
don't want to have to manually delete the ' symbol from each and ever
cell. Can anyone please tell me a quick and easy way of removing thi
and making the cells 'normal' numbers again?

Thank you if you can!

 

[David McRitchie]

Without macros you can copy an empty (never used) cell with Ctrl+C
then select the cells or column(s) you want the change from text
to numbers, then Edit, Paste Special, Add

If you have to do this often you might look at the TrimALL macro on
my http://www.mvps.org/dmcritchie/excel/join.htm#trimall
or one of the ReEnter macros.