Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

Is there a way within excel to clear a sheet of all numbers, but leave behind text and formulas?

To elaborate: I mean cells that contain ONLY numbers. If a cell contains text and numbers (like "Period 1") it can stay.

Edit: Just to be clear: I'm not asking for the values that formulas evaluate to to remain. that would be silly. I am asking for the formulas themselves to remain. the text of the formula. So that the zeroed page still has formulas ready for data to be added to the zeroed cells. and produce the result of the formula.

share|improve this question
The formulas would automatically recalculate, so you can't possible get rid of all the numbers and leave formulas. – Lance Roberts Jun 13 '11 at 3:37
You misunderstand me. I know the formulas will re-calculate but I want the formula to remain, not the data it calculates to. So if cell a1 contained '345 and cell 2 contained '678' ' and cell a3 contained '=(a1 + a2)' I want cells a1 and a2 to be empty and cell a3 to still contain '=(a1 + a2)'.. so that in the future when cells a1 and a2 are populated again the sum in a3 is still present to produce a value – MrVimes Jun 13 '11 at 3:44

1 Answer

up vote 5 down vote accepted
  1. Select the region or entire sheet where you want to clear the numbers.
  2. Press Ctrl + G to bring uo the Go To dialog box.
  3. Select Special...
  4. Select Constants and Numbers (be sure to deselect Text, Logicals and Errors).
  5. Select OK - now your selection will be reduced to just the cells that contain numbers.
  6. Press Delete to delete the contents of the selected cells.

Excel's Go To Special dialog box

share|improve this answer
BRILLIANT! Thanks! – MrVimes Jun 13 '11 at 4:04
Seems like Deletion is a little extreme, maybe just right click and 'Clear Contents' on the selection. But since it works for the OP, I guess it's ok. – Lance Roberts Jun 13 '11 at 4:34
Deletion works for me. If it deletes anything it shouldn't I can just ctrl-z. I have a file containing many sheets each containing lots of text and formulas and data. We 'reuse' the same file for new periods so this requires removing the data but keeping the formulas. Mike's solution saves me a LOT of time and hassle. – MrVimes Jun 13 '11 at 4:45
1  
@Lance Roberts, as I understand it, pressing the Delete key on a selection is the same as clearing the contents. Right-clicking and selecting Delete (shifting cells up/left) is much more extreme, but that wan't what I was suggesting. I've rephrased step 6 to make this a little clearer. – Mike Fitzpatrick Jun 13 '11 at 4:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.