+ Reply to Thread
Results 1 to 23 of 23

Delete cell contents but not formula?

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Delete cell contents but not formula?

    Hi
    How do I delete the contents of a cell but not the formula that is in the cell?
    Thanks...

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Delete cell contents but not formula?

    That's not possible. You could delete the data in the cells the formula is calculating from. You can also make a cell look empty if a certain condition within the formula is met, what does for formula look like?
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete cell contents but not formula?

    Can you post an example of what you want to do?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    I would like to delete the data in the cells the formula is calculating from.

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Delete cell contents but not formula?

    Quote Originally Posted by dontaylor View Post
    I would like to delete the data in the cells the formula is calculating from.
    That's neat, go do that.




    Please post a question, if you are looking for an answer. Provide examples, samples. Remember, we can't read your mind or see your screen.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Delete cell contents but not formula?

    All you need to do, is delete that data. If this causes your formula to produce an error or 0, you can fix that by adjusting your formula to something like...

    =IF(the-cell-you-deleted="","",your-formula)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    12-15-2011
    Location
    Washington
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Delete cell contents but not formula?

    I'm with FDibbins, just delete the data and use the IF. If the formula is complex and relies on many cells then deleting just a few may result in #ERR or #N/A (or a multitude or other #problems). To make it pretty I usually use IF(ISERROR(your-formula),"[anything you want]",your-formula).

  8. #8
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete cell contents but not formula?

    If you post a sample formula that is returning an unwanted error, we'll see if we can help restructure it.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Delete cell contents but not formula?

    dingdong, I see that you are using 2013, that has the IFERROR() function available to you, so the formula then necomes...
    =IFERROR(your-formula,"[anything you want]")


  10. #10
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    Hi Ron

    Example as requested.Example.xlsmExample.xlsm

  11. #11
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete cell contents but not formula?

    Oh, for goodness sake...You're running a MACRO!
    • Right-click on the CLEAR button
    • Select Assign Macro
    ...Click: Edit
    • Replace your code with this code:
    Please Login or Register  to view this content.
    The current code clears columns B:G...instead of B:E

    Does that help?

  12. #12
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    example.xlsxOK, I wll try the macro later.

    Please see the attached example for the original problem of how to delete the contents of a cell without deleting the formula.

    Thanks...

  13. #13
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete cell contents but not formula?

    Please explain exactly what you mean by "clear the contents of I8 without deleting the formula".
    Using your example
    G8: 4
    H8: 5
    I8: =SUM(G8+H8)
    ...which is probably the wrong way to do that.
    ...it should be =SUM(G8:H8)

    So...using that example.
    What do you want to do and what do you want to see after the adjustment?

  14. #14
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    Hi

    After the calculation the contents of cell i8 need to be deleted to allow the next calculation. when i8 is highlighted and you press delete the formula is deleted along with the contents.

    So, How do I delete the contents of a cell without deleting the formula that is contained in the cell?

  15. #15
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Delete cell contents but not formula?

    So, How do I delete the contents of a cell without deleting the formula that is contained in the cell?

    If a cell has a formula in it, then the formula IS the contents of a cell. Please rephrase your question, you are basically asking how do i delete something without deleting it.

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Delete cell contents but not formula?

    After the calculation the contents of cell i8 need to be deleted to allow the next calculation.
    This does not make sense to me. The way spreadsheets work, there should be no need to do anything to I8 to prepare it for next calculation. Assuming calculation mode is set to automatic, anytime G8 or H8 change, I8 should automatically update to reflect the change. Perhaps if you explain in more detail what you mean by "allowing the next calculation" in I8, we can better understand what you are trying to convey with this question.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Delete cell contents but not formula?

    I've tried to combine the answers previously given to you. You have given two examples so because they are quite different two files are returned.

    Maybe you are just a misunderstanding what has been provided to you. At the same time, your instructions are confusing. In one instance you imply that you want the form cleared without deleting the formulae and in another instance you refer to a single cell.

    The macro file will clear the whole form.
    Attached Files Attached Files
    Last edited by newdoverman; 10-21-2014 at 11:13 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  18. #18
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    all I want to do is highlight a cell and press delete or clear and the value in the cell disappears but the formula stays in the cell.

  19. #19
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete cell contents but not formula?

    One last attempt before my pliers wear out...
    You cannot delete a formula...and HAVE a formula.
    Do you want the formula in I8 to show nothing if there are no numbers in G8 and H8?
    If yes: this formula returns an "empty cell" if G8 and H8 do not contain numbers
    Please Login or Register  to view this content.

  20. #20
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Delete cell contents but not formula?

    Not sure exactly how do you want to achieve that but please find the attached sheet and click on the buttons to see if this is something you are trying to achieve since this is the only solution which strikes to my mind after reading all your replies.
    Attached Files Attached Files
    Last edited by sktneer; 10-21-2014 at 01:03 PM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  21. #21
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    OK - Thanks, I will try this one.
    I guess it can't be done by pressing delete on the keyboard, thanks for your help.
    The code for the macro clear works perfectly.
    Thanks again.

  22. #22
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Delete cell contents but not formula?

    Thank you Sktneer, this is a very good solution.

  23. #23
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Delete cell contents but not formula?

    You're welcome. Glad I could help and thanks for the feedback as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro Delete Contents in 23rd corresponding cell if the cell in range is a non-blank cell
    By murtaza.khan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-04-2014, 01:11 AM
  2. How do I delete the contents in a cell, every other row?
    By ninj5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2013, 05:49 PM
  3. [SOLVED] Code to delete cell contents IF the result of a formula in the cell = 0
    By jayjaynz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-22-2013, 03:58 AM
  4. Delete cell contents
    By jsomer2000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2011, 08:38 AM
  5. Find cell based on formula result and delete contents
    By Big Chris in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2006, 10:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1