7

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.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
JC.
  • 307
  • 1
  • 5
  • 11

8 Answers8

16
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

Chuck Dickens
  • 309
  • 1
  • 9
  • An issue with `.Text` is that if the column of the source range is not wide enough, the text displayed will be "####...". Using `Format()` doesn't have this issue. Related : https://stackoverflow.com/questions/76484321/how-can-i-concatenate-a-bold-text-date-and-time-into-a-specific-cell/76484547#76484547 – DecimalTurn Jun 15 '23 at 20:11
  • Why are you commenting on a 14 year old answer? Just for reputation? – Chuck Dickens Jun 17 '23 at 12:05
  • Just because an answer is old doesn't mean it can't be improved. I'm commenting to add information that is missing from your answer. If you think this information is relevant, feel free to mention/address it in your answer and I'll gladly delete my comment(s). – DecimalTurn Jun 17 '23 at 13:23
4

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

dpmattingly
  • 1,301
  • 1
  • 7
  • 11
4

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
mandroid
  • 2,308
  • 5
  • 24
  • 37
2

"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)?

Kursad Gulseven
  • 1,978
  • 1
  • 24
  • 26
anonym
  • 21
  • 1
1

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.

Ryan Shannon
  • 710
  • 1
  • 5
  • 9
0

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.

0

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.

Bryan
  • 1,851
  • 11
  • 33
  • 56
0

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!

MVO
  • 1