0

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

  • Sorry, what is the question? You want to apply the given formula to cell A1? Put =TEXT(A1,IF(A1-INT(A1)=0,"0","#.####")) into B1 or any empty cell. – Alex M Oct 22 '19 at 16:55
  • I want to apply the given formula to a block of cells. – Lasuiqw Oct 22 '19 at 16:56
  • If you want to deal with duplicates then a number format will not do it for you, as it only masks the true value. Excel will use the true value not the mask when comparing values. You will need to change the number itself, either through a formula or vba. – Scott Craner Oct 22 '19 at 16:58
  • Noted, using number format won't fix the problem. – Lasuiqw Oct 22 '19 at 16:58
  • @Lasuiqw,, attached StackOverflow questions are different stuffs (convert currency value from one to other),, the formula you have used, works nicely,, no need to use Paste Special,,, better Edit your post and add sample data for better understanding,, to fix it !! – Rajesh S Oct 23 '19 at 7:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.