Feb 24 2021 12:01 PM
Feb 24 2021 12:01 PM
I was editing the results of a formula in multiple cells in a column (=A1&""&B1) gives the result AABBCC301.79 (where A1=AABBCC and B1=301.79) . I wanted to remove the fractions so the result would be AABBCC301. There were multiple cells to edit in this fashion. When first editing I would click on a cell and remove the decimal digits using the backspace key (the formula showed in the formula bar and the result showed in the cell when in edit mode). I decided to automate this task using a macro. After recording the macro, I saved the workbook and was told it had to be saved as an "Excel Macro - Enabled Workbook" instead of just an "Excel Workbook".
Now when I try to edit any cell with a formula in Excel, whenever I click on the cell, the cell changes from the result to the formula and I cannot edit it. Ex. AABBCC301.79 becomes =A1&""&B1) when in edit mode, and I cannot remove the decimal numbers.
I tried all the suggestions about changing the format to General, text and number. I tried deleting the = and then re-adding = to the formula. I tried uninstalling and reinstalling Excel. Tried removing the Developer Add-In (that enables Macros). Nothing has resolved the issue. The same thing happens now if I change the formula (=A2+B2), gives the result 4.79. When I enter the cell to remove the decimal point the result disappears and only the formula shows. If the result is a number I can change the number format to zero decimal places and that works. I the result is AABBCC301.79 removing the decimal places in the number format does not work.
The same thing happens if I create a new workbook.
Feb 24 2021 01:56 PM
Thanks Hans for the reply,
That did not work. If A1=5.25 and B1=5.5, that formula returns 10 (no decimals - the point of INT, but when I enter the cell to edit the resulting number, it still only shows the formula.
If A1=AABBCC and B1=301.25 that formula using INT returns #VALUE! This is the formula I need to work - a combination of text from one cell and numbers in the other. I need to be able to edit the result so it reads AABBCC301
I also just noticed that I can no longer use Find and Replace to edit the result of a formula i.e. I cannot find AABBCC301.25 and replace it with BBCCDD301.25, I have to find A1 (AABBCC) and replace it with A2 (BBCCDD).
Feb 24 2021 04:06 PM
Using the formula =A1&B1:
Press F2 to edit the cell.
You will see the formula.
Select it in its entirety.
Press F9 to evaluate it.
Now you see the result.
Edit it as needed.
Warning: this destroys the formula; only the edited result will remain.