How can I copy the formatted value and paste as a new value(not the original value with formatting)?

[Daniel]

Here are the examples about the problem which I am facing.

eg1.
The original value in the cell: 4/23/04
Formatted cell(DD/MM/YYYY): 23/04/2004 (In this case, the value is
still 4/23/04)
Expected: the value and the outlook are also "23/04/2004"

eg2.
The original value in the cell: 4.332
Formatted cell(truncate with 2 digits): 4.33

What I am now going to do is changing the value of cells with new
format because a new system of my computer need to extract the value
from the excel with specified format. But changing the format in excel
doesn't really change the value of the cell. I have tried all the
function in paste special and they cannot solve me problem.

Is there any way to do other than re-entering the value?

Thanks

 

[Guest]

Hi
formats never change the underlying value. If you need it in a specific
format you may try using the TEXT function in a helper column e.g.
=TEXT(A1,"DD/MM/YYYY")