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

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.

Answer 1

I believe you can use the TEXT function to format a date value to your liking.

The format string of "dd-mmm-yy" would format "05/11/09" as "11-MAY-09".

Answer 2

Try this:

Sub FormattedText()
    Dim r As Range

    On Error Resume Next
    Set r = Application.InputBox(prompt:="Select cell", Type:=8)
    If r.Count <> 1 Or r Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0

    ActiveCell = "'" & r.Text

End Sub

It will put text of a selected cell (prompted) in the active cell.

Answer 3

You should be able to right click on the cell and set the format as General. This will allow you to put something in without it being automatically formatted to something else.

To save yourself from copying and pasting you will also want to start by putting in the date you want and not formatting and then copying.

Answer 4

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" & i) = Range("A"& i)
Range("B" & i).NumberFormat = "dd-mmm-yyyy hh:mm:ss" 'Date as 10-Jun-2005

Another way to do it.

Answer 5

Use the Format function.

Format("5/11/2009", "DD-MMM-YY")

This will return:

11-May-09

If case matters:

UCase(Format("5/11/2009", "DD-MMM-YY"))

returns:

11-MAY-09

Answer 6

Range("B1").Value = Range("A1").Text

Using the cell's .text instead of .value modifier will copy the text formatting instead of the raw date.

Chuck