Excel apply complicated formula to existing cells value without copy/paste

Question

I want to apply the formula

=TEXT(A1,IF(A1-INT(A1)=0,"0","#.####"))

to existing cells with numbers without doing copy and paste.

The existing spreadsheet is already big and filled out and I don't want to create another copy of it and then deal with removal of the duplicate.

I've tried using paste special. And there's the similar question on stackoverflow: https://stackoverflow.com/questions/21560480/excel-apply-formula-to-existing-cells-value-without-copy-paste but that uses arithmetic operations and this algorithm/formula I'm applying is more complicated. I've asked Reduce Significant Figures in Excel.

On https://stackoverflow.com/questions/58507643/excel-apply-complicated-formula-to-existing-cells-value-without-copy-paste?noredirect=1#comment103343484_58507643, I heard there's a way to do this using format cells, custom, specifying a number format code, something that looks like

,##0_);(#,##0).

The problem is I'm not familiar with the language of number format codes.

Shortcut to cell format

Answer 1

If you just want to solve your original problem:

Format entire Excel spreadsheet so there are only 3 digits after decimal points

the second answer has a method that does not change any actual values and can, therefore, be fairly easily applied to all cells with no special training.

It also gives the precise, copiable strings for the date formats suggested so they can just be pasted in where needed.

Your second asking of the question produced this text change approach. If you do it, the underlying data will be changed to text. You seemed happy with that, so... BUT it cannot be done as you wish.

The problem is that you cannot use something like formatting, or even Paste|Special (if you were able to copy and paste) to change something to fit into and be acted upon by a formula. It cannot be done in Excel.

You CAN do at least two things though to achieve the effect you desire, though the second is a copy and paste technique. (Unfortunately, the other uses VBA and if I were a betting man, your unfamiliarity with date formatting, said so in this question, would make me think VBA is not something you use often.)

1) Write a macro that checks for the end of data so it doesn't have to work on billions of cells with nothing when that can be known and avoided. Then have it begin at that end and work back along the row (or up that column to start though row is easier) basically first seeing if the cell is blank, and if not, setting a variable equal to the cell's original contents, then placing the contents in a string made of the formula you have here, then setting the cell's contents to be a formula whose text is that string. Then have it move left a cell (this would happen immediately after finding it is blank) and do it all again. When if finishes column A for that row have it go back to the original column, up a row, and keep on trucking.

It would make each change one at a time, so almost no effort on its part, rather than trying to change 43,000,000 (or however many) at once like a copy and paste kind of approach might do which might overload your machine (I assume that overload concern is the reason you don't want to copy and paste anything.)

2) This is the copy and paste approach. Perhaps that wasn't the concern (overloading, choking Excel to a stop, maybe losing your file) but rather you are thinking you'd have to do a few million copy and pastes by hand. But if you are willing or able, create a new worksheet, find the end of data on the current worksheet (press Ctrl-End) and note the cell address, go to the new sheet, its cell A1, type in this formula and when you need to type in cell A1's address, use your mouse to click on cell A1 of the current sheet (so you avoid typing sheet names and so on), then save the cell with ENTER.

Now copy it and go to the cell address on this new sheet that matches the end of data on the other sheet. Press Shift-Ctrl-Home to highlight everything back up to A1. Copy that region. (If Excel balks at that, you'll need to do it in chunks.) Go back to the orgiinal sheet's cell A1 and choose Paste|Special|Values by whichever of the 5-6 ways to do it you prefer.

Done.

But if you want a faster, easier method which keeps the data precisely as it now is, just changes the appearance of it, check out the answer I gave on your earlier question.