How to remove the formula but leave the value

[jim]

How do you take a cell with a formula that has returned a value and remove
the formula but leave the value?

Thanks,

Jim

 

[Norman Harker]

Hi Jim!

Chip and John have given the answer of Copy followed by Paste Special
Values.

I find that I use this so much that it is worth bring the Paste
Special Values button up to the toolbar close to where the copy button
sits.

Then it's just a case of selecting my cell or cells and clicking the
Copy button followed by the Paste Special Values button.

To pull it up:

View > Toolbars > Customize
It's in the Edit section. (Clearly labeled "Paste Values")
Click and drag it to where you want it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Tuesday 29th July: Dominican Republic (Father
’s Day), Faroe Islands (Olavsoka Day), Haiti (Maitresse Silverine),
Norway (St. Olav’s Day), Peru (Aymaraes). Observances: Maitresse
Silverine (Voudon).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[CLR]

If it's a relatively small number, or simple text, you can just highlight
the cell and type the same number or text right back over it and the formula
will go away and the value will stay.......

Vaya con Dios,
Chuck, CABGx3

 

[JMay]

Not thoroughly tested but:

Sub ConvertToValue() ' Converts the Current Cell Formula to its Value
ActiveCell.Value = ActiveCell.Value
End Sub

in a standard module works for me -- And assign (the Code) it to an Icon on
the toolbar.. Select the formularized Cell you wish to Convert and Click
the Icon, Done

 

[jim]

Thanks, just what I was looking for. Jim

Not thoroughly tested but:

Sub ConvertToValue() ' Converts the Current Cell Formula to its Value
ActiveCell.Value = ActiveCell.Value
End Sub

in a standard module works for me -- And assign (the Code) it to an Icon on
the toolbar.. Select the formularized Cell you wish to Convert and Click
the Icon, Done

 

[Stephen Dunn]

Another quick way for the mouse users out there:

Select the cells, drag a border to one side and back again using the right
mouse button, then click Copy Here As Values Only.

 

[David McRitchie]

Hi Jim,

If you want a macro (turned into a programming question) why limit
yourself to the active cell, when you can use a selection that can
be one cell, rows, columns, entire sheet. You have to be
careful with a single cell selection but you can read more abbot
coding macro for that in
.http://www.mvps.org/dmcritchie/excel/proper.htm

Sub MakeValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlFormulas))
Cell.Value = Cell.Value
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Use of Special cells automatically limits to the used range,
and xlFormulas restricts processing to formulas. The
Intersect prevents a single cell selection from expanding to
the current region. Turning off Calculation speeds processing,
turning off screen updating tells VBA for Excel not show results until done.

A variation would be to copy the sheet with a macro and change
the cells in the added worksheet to constants so that your original
sheet is unaffected.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Stephen Dunn]

Hi Norman,

As someone who rarely moves from the keyboard, I surprised myself by
remembering that one. Actually, I'm sure I first read it in this group,
where so many small things jump out as "That's great! I never knew that!",
only to be forgotten again a week later...

Regards
Steve D.

Hi Stephen!

Nice one! Never used it that way before. I'm collecting quite a few of
these "mousecuts" now.

In passing it's surprising in groups of Excel users (not just the
beginners) to find out how many don't know of, or rarely use, the
right click buttons. Even for common tasks like right clicking the
Start button to get to Explorer.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 31st July: Bermuda (Cup Match Day),
Congo (Upswing of the Revolution), Mexico (Day of Mourning), Peru (San
Ignacio). Observances: Lunasa / Lammas (Pagan N. Hemisphere), Oimelc /
Brigid (Pagan S. Hemisphere)
(e-mail address removed)

<snip>