CODEWITHSUNDEEP
excelvbadatecopyformat
JC.
JC.

Reputation: 317

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

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.

Upvotes: 8

Views: 20523

Answers (8)

anonym
anonym

Reputation: 21

"The same answer but with a different function (that has worked for me):

Public Function DisplayText(ByVal pRange As Range) As String  
  DisplayText = pRange.Text  
End Function

Just use =DisplayText(A1). If you change the cell format this function will return the displayed text"

  • cc: alvaroc

How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?

Upvotes: 2

MVO
MVO

Reputation: 11

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!

Upvotes: 1

mandroid
mandroid

Reputation: 2338

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

Upvotes: 4

Chuck Dickens
Chuck Dickens

Reputation: 329

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

Upvotes: 18

Ryan Shannon
Ryan Shannon

Reputation: 710

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.

Upvotes: 1

Bryan
Bryan

Reputation: 1893

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.

Upvotes: 0

Duncan
Duncan

Reputation:

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.

Upvotes: 0

dpmattingly
dpmattingly

Reputation: 1321

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".

Upvotes: 4

Related Questions