How can I obtain the displayed value instead of actual value in Excel?

By : JC.
Source: Stackoverflow.com
Question!

I have a cell containing a date ex. "05/11/09" It is currently displayed as "11-MAY-09". How do I copy-paste or use VBA to get the string "11-MAY-09" into the cell next to it ( NOT "05/11/09")?

I can't figure it out other than piecing out the date pieces by itself.

By : JC.


Answers

In VBA you can do this:

Range("B2") = Range("A2")
Range("B2").NumberFormat = "dd-mmm-yyyy hh:mm:ss" 'Date as 10-Jun-2005

If you need to loop it then:

Range("B" 
By : Bryan


Low-tech but very easy way - paste it into Word, then copy back into Excel! Can take a while with a big file, however... buts works great for one-off uses!

By : MVO


This video can help you solving your question :)
By: admin