Delete text in cells but keep formula

I currently have an excel grid with headers in rows and columns. Within the
cells there is data that I dont need, but I do need the formulas in those
cells, how do I clear the data in the cells without deleting the formulas?

( I have tried GO TO - Special - Constants but this clears all of my headers
rows and additional info that I need, I only want to clear the text in the
cells that currently contain formulas)

Hi YanYan,
See http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To clear out constants in a selection range, something like one of the following::

Selection.EntireRow.SpecialCells(xlConstants).ClearContents

Selection.SpecialCells(xlConstants).ClearContents

You could protect some cells with constants by changing them to formulas
Range("A3:A3000").EntireRow.SpecialCells(xlConstants).ClearContents .

The use of SpecialCells automatically limits you to the used range.
There is a problem with with Special Cells that you might encounter in an
extreme range and one that you should be very concerned about when
deleting content. Actually the failure is more than 8192 areas within a range
Failure if more than 8192 cells selected by specialcells
see MS KB http://support.microsoft.com/?kbid=832293
and there is no undo from within a macro, so even if you see that everything
is deleted you can't undo.
---
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

"YanYan" <YanYan@discussions.microsoft.com> wrote in message news:DE97D435-3D9C-4EDC-A102-6860D042EB39@microsoft.com...
> I currently have an excel grid with headers in rows and columns. Within the
> cells there is data that I dont need, but I do need the formulas in those
> cells, how do I clear the data in the cells without deleting the formulas?
>
> ( I have tried GO TO - Special - Constants but this clears all of my headers
> rows and additional info that I need, I only want to clear the text in the
> cells that currently contain formulas)

If the text is the result of formulas, you are stuck, unless you re-write
all the formulas in the form

=IF(SomeFlag=specific value,old formula,"")

HTH,
Bernie
MS Excel MVP


"YanYan" <YanYan@discussions.microsoft.com> wrote in message
news:DE97D435-3D9C-4EDC-A102-6860D042EB39@microsoft.com...
>I currently have an excel grid with headers in rows and columns. Within the
> cells there is data that I dont need, but I do need the formulas in those
> cells, how do I clear the data in the cells without deleting the formulas?
>
> ( I have tried GO TO - Special - Constants but this clears all of my
> headers
> rows and additional info that I need, I only want to clear the text in the
> cells that currently contain formulas)

No the text is not the result of formulas, just data that has been entered
into the cell to help describe the formula, but I dont need it because this
sheet is merging with another sheet and all that mumbojumbo that I dont need
shows up.

"Bernie Deitrick" wrote:

> If the text is the result of formulas, you are stuck, unless you re-write
> all the formulas in the form
>
> =IF(SomeFlag=specific value,old formula,"")
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "YanYan" <YanYan@discussions.microsoft.com> wrote in message
> news:DE97D435-3D9C-4EDC-A102-6860D042EB39@microsoft.com...
> >I currently have an excel grid with headers in rows and columns. Within the
> > cells there is data that I dont need, but I do need the formulas in those
> > cells, how do I clear the data in the cells without deleting the formulas?
> >
> > ( I have tried GO TO - Special - Constants but this clears all of my
> > headers
> > rows and additional info that I need, I only want to clear the text in the
> > cells that currently contain formulas)
>
>
>

Then use Go To....- Special - Constants... but to not clear all of your headers, first select only
the cells that currently should have formulas.

HTH,
Bernie
MS Excel MVP


"YanYan" <YanYan@discussions.microsoft.com> wrote in message
news:E033B9C5-4857-4C11-B74E-B43022AFE3B6@microsoft.com...
> No the text is not the result of formulas, just data that has been entered
> into the cell to help describe the formula, but I dont need it because this
> sheet is merging with another sheet and all that mumbojumbo that I dont need
> shows up.
>
> "Bernie Deitrick" wrote:
>
>> If the text is the result of formulas, you are stuck, unless you re-write
>> all the formulas in the form
>>
>> =IF(SomeFlag=specific value,old formula,"")
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "YanYan" <YanYan@discussions.microsoft.com> wrote in message
>> news:DE97D435-3D9C-4EDC-A102-6860D042EB39@microsoft.com...
>> >I currently have an excel grid with headers in rows and columns. Within the
>> > cells there is data that I dont need, but I do need the formulas in those
>> > cells, how do I clear the data in the cells without deleting the formulas?
>> >
>> > ( I have tried GO TO - Special - Constants but this clears all of my
>> > headers
>> > rows and additional info that I need, I only want to clear the text in the
>> > cells that currently contain formulas)
>>
>>
>>