I have been getting a lot of hits on this particular article. So, I thought I would update it by adding some additional images and a bit more clarity to the subject. I hope you enjoy this article and can benefit from it. Continue reading the main article below and my other articles listed on the left side of this page. And leave a comment if you don’t mind. Thanks.)
If you have used Microsoft Excel, or even Open Office Calc (the free alternative to Microsoft Excel) you have most likely used or even created formulas to return a desired value. An example would be;
Column A contains a latitude, Column B contains a longitude and Column C has a formula that concatenates the two together. Here is a sample of that formula in column C:
In the formula above, notice the three commas and space between the two quotation marks following the middle comma. Anything you put between the quotes will be shown in Column C, but the quotation marks will not appear. See the sample below.
In this sample, we will put the word “Hello” within the quotes.
And here are the results of that same cell in the Excel sheet.
Now, getting back to our real-world example.
Given the latitude of 34.6528144 and the longitude of -98.4146529 in columns A and B respectively, you get the following result displayed in column C.
As you can see, the results contain the comma and space (between the two numbers) that was specified in the formula between the quotation marks.
Okay, I know this article is supposed to be about how to remove the formulas from a calculated cell (or cells) while retaining the resulting data. Instead, all I have discussed so far is how to create a formula in a cell. True, but to remove a stain, you must first understand what the stain is and how it got there. The same theory holds true in a substantial number of aspects of life.
With that said, let’s remove those pesky formulas now that we are done with them. There are many ways you can accomplish this, and I have seen countless chapters in Excel books and dozens of web sites that have you find a specific tool on the toolbar and navigate through a dozen or more options to get your desired results. However, the simplest way is to just select the cell or cells that contain the formulas and press “Ctrl” + “C” to copy them. Next, with the cells selected, press “Ctrl” + “V” to paste the resulting values in the same cells.
Before you just hit the “Enter” key or move on to other things in your life, notice the little “clipboard” looking icon that popped up. It should look something like this: (I know the image is small, but that’s the way it is in Excel, so bare with me.)
Click on it and scroll down the menu to the “Paste Values” section of the list and choose the “Values (V)” option.
Hint, as you mouse over the options (represented by small icons), a tool tip will appear telling you what that function is called. Once you click the “Values (V)” icon, all formulas will be erased and replaced with only the results that it calculated. See the results in the two images below.
Image 1, Before
Image 2, After
Mystery solved! It really is a lot simpler than a lot of web pages make it out to be. As you can tell, I am a firm believer in the K. I. S. S. (Keep It Simple Stupid) method of thought.
Thank you for viewing my blog.
Also visit my new “About me” blog at https://www.aubreywlove.com