Geek Answers Handbook

How to get display value instead of actual value in Excel?

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

I can't figure it out except to cut out the pieces of the date myself.

+5
source share
8 answers

Use the "Format" function.

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

This will return:

11-May-09

If business matters:

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

returns:

11-May-09
+3
source 
Range("B1").Value = Range("A1").Text

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

Chuck

+6

, TEXT .

"dd-mmm-yy" "05/11/09" "11-MAY-09".

+4

:

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

() .

+1

. - , - .

, , , .

0

VBA :

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

, :

Range("B" & i) = Range("A"& i)
Range("B" & i).NumberFormat = "dd-mmm-yyyy hh:mm:ss" 'Date as 10-Jun-2005

.

0

, - Word, Excel! , ... !

0

" , ( ):

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

=DisplayText(A1). , "

  • cc: alvaroc

MS Excel ( , )?

0

More articles:


All Articles