Surgically Replacing Parts of Formulas in Excel

Problem

Sometimes there are parts of an overall formula that need to be removed from a number of cells all at once but a simple find and replace will result in an error.
To try and find a fix I went on a fruitless google hunt with the following searches:

• replace only part of a cell in excel
• find and replace with wildcards in excel
• advanced find and replace excel
• find and replace the beginning and end of a formula

The best results involved VBA, but I knew there had to be something better.

It might seem easy enough to just use the Ctrl + H and replace what you need to with a single short cut. The tough part is when the formula has multiple criteria that you need to remove, it will produce an error while trying to calculate the new cell right after replacement.

After thinking about it for a while and breaking down the error, this is what I came up with as a work around.

Solution

The key step in allowing specific portions of a function to be removed in multiple cells is by turning the cells into text. The character that is telling the program to calculate each cell is the "=" at the start of the cell. The process I have to remove specific sections of a formula and restore the formula into working order consists of converting it to text, using find and replace to edit the text in multiple cells, and then restoring the text to an equation by inserting a "=" in the front again. Here's what I mean.
In the specific project I'm working on I want to remove the ROUND function from a large table of cells.

1. Select the cells with the formula you would like to edit, go to the ribbon, click on Find & Select,  and click on Replace.
   
2. In the Find what field enter: =
   In the Replace what field enter a unique string such as: SPOCK
   Press the "Replace All"
3. With the cells you need edited still selected, in the Find What field enter the first part of the formula you would like changed with what you would like it changed to in the Replace with field.
   In this case I will replace "+ROUND" with nothing to get rid of rounding.
   
4. Repeat step 3 with any other parts of the equation you needed changed. I will remove the end of the Round function ",0)" with ")" to remove all rounding from these cells. This step can be done as many times as you need to edit the formula, but it will be key to leave the unique string you entered in in step 2 for the final step.
5. For the final step to restore it back to a functioning equation, use the Find and Replace tool to replace your unique string with an "=". In my example I will replace SPOCK with "=."
   

If all worked you will have a group of newly designed equations that have been adjusted to exactly what you need.  

Have you ever run into this 

No Way to Save

Problem

Sometimes Excel acts like Snapchat.

Snapchat doesn't save files or updates like other social networks. Recently Excel has been acting rather similar. I don't know if it is some weird program envy or if my employer is trying to just keep me at work longer so I can' t shut down, but it has proven to be a problem.

The problem started when I was working on a worksheet with a large data set and many linked equations. It was sucking up processing power and it felt like I was trying to run in jeans.

I updated all tables and reports and proceeded to save the file when this popped up:

You can get your magnifying glass out, but really it just says "Errors were detected while saving..."

I don't care so I press continue, and then this pops up.

Document not Saved Error

I tried a few more times with no luck, so I have this work around.

Solution

Copy and Paste.

I have a habit of saving versions of a file.  Each time I update a workbook I save as a version with that day's date in the title. This came in key here. With the current date version open I opened a prior version.

From there I copy and pasted the updated values from the file that would not save and saved the new file as a new name.  

This fix would also work if you wanted to open a blank worksheet and copy all the data over. However, if you have formatted tables in your report you would have to convert them into ranges first.

That was my fix, but have you had this problem? How have you fixed a file that wouldn't save?

Power Pivot Error

Problem

Recently I was revamping our production management tool with the new bells and whistles from Excel 2016. It was working beautifully until I removed a table linked to the Data Model.


When the table was removed before first removing it from the data model I had created the data model in Excel 2016 was continuing to search for it whenever I refreshed the data or tried to save the file.

This resulted in a really annoying error:

We couldn't Get Data from the Data Model. Here's the error message we got:
Object reference not set to an instance of an object.

I thought it was adorable how the error message was pushing responsibility off to another error message... hey I don't know what this means but you got this error message ... good luck.

I tried a few different ways to get to the data model and delete the reference to the now deleted table. Unfortunately, these attempts were as successful as singing career.  However, there was a solution I was able to use, though you may not like it.

Solution

I saw the problem as a communication error with a new feature in Excel 2016. So, being a fan of time travel, to fix it I took the worksheet up to 88mph and back to a simpler time, to 2003 to be exact.

I saved it as a former version of Excel.

This broke the link to the data model as well as all the other functions I had created with tools from the versions since 2003. It is the loss of the additional tools that makes this solution less than ideal. 

However, after re-opening the file and saving it again as a current version all of my data remained and I just had to add some slicers and other formatting changes and I was back up and running.

Have you had this issue? Leave a comment below and let me know how you solved it.