Forum Discussion
Converting number format to cell value
Hi folks - curious if there's a clever way to convert a number's format to become the cell value.
ie: we have a cell value of 85, and we apply a number format to represent 2 decimal places, to 85.00
Is there a clever way to update the cell value to 85.00 (instead of the value being 85)? When I copy the formatted cell, and paste the value, it pastes 85 instead.
If you copy and paste a formatted cell, you'll copy the format too.
If you copy a cell and paste as Values, you get only the value, not the formatting - that's the point of paste as Values.
You could do the following. Let's say you have 85 in cell D2.
In E2, enter the formula =TEXT(D2, "0.00"). You'll see 85.00 in E2 as a text value.
You can now copy E2 instead of D2, and paste as Values. That will result in 85.00.
4 Replies
If you copy and paste a formatted cell, you'll copy the format too.
If you copy a cell and paste as Values, you get only the value, not the formatting - that's the point of paste as Values.
You could do the following. Let's say you have 85 in cell D2.
In E2, enter the formula =TEXT(D2, "0.00"). You'll see 85.00 in E2 as a text value.
You can now copy E2 instead of D2, and paste as Values. That will result in 85.00.
- Thanks so much! Converting to text works, and I appreciate the fix! I'm curious though, is there a way for the cell value to be a number, and for the value to be, "85.00," without using formatting?
If a cell contains 85 as a number, that 85 is the value of the cell, regardless of how that value is displayed in the cell, whether that is 85 or 85.0 or 85.00 or 8500% or 8.5E+01.
85.0 or 85.00 etc. are not the value of the cell, just the way it is displayed.
If a cell contains text, its value is exactly what is displayed.
