Paste a value in Excel exactly as is visible

user_4650297 0
Viewed 238

I have a simple request, to paste the data exactly as visible in Excel.

I have a list of dates in mm/yyyy format, but Excel keeps adding mm/dd/yyyy which is throwing off my analysis. It's formatted to show simply mm/yyyy but the actual cell value keeps getting set to mm/01/yyyy.

How can I simply copy/paste the value to be mm/yyyy.

enter image description here

I've tried Range("A1").Value = Range("A1").Value, but of course that just keeps the same info.

Yes, in my case since it's dates, I can do a kludgy function that takes the left three characters, and combines with the rightmost four. However, that really just gets the date number returned. I tried on G4 and get 4171730. Plus, I'd like to know how to do this with other types of cell values too (strings, numbers, etc.).

vba excel
2 Answers

save the value and the format then set the cell as text and assign the formatted value:

Sub test()
    Dim t As Variant
    t = Range("A1").Value2

    Dim x As String
    x = Range("A1").NumberFormat

    Range("A1").NumberFormat = "@"
    Range("A1").Value = Format(t, x)

End Sub

This also works

Sub test()
    Dim t As String
    t = Range("A1").Text

    Range("A1").NumberFormat = "@"
    Range("A1").Value = t

End Sub
user_48515900 
 Range("A1").Value = Format(yourdate, "mm/yyyy")
user_50092930
Related