Pasting Text

When pasting text from other applications into Excel, it is parsed according to the WRS. There is no way to tell Excel the number and date formats and language to recognize. The only workaround is to use a DataObject to retrieve the text from the clipboard, parse it yourself in VBA, then write the result to the sheet. For clarity, the following example assumes that the clipboard contains a single U.S.-formatted number and that it should be enhanced to check for U.S.-formatted dates as well:

Sub

ParsePastedNumber()

Dim oDO As DataObject

Dim sText As String

'Create a new data object

Set oDO = New DataObject

'Read the contents of the clipboard into the DataObject

oDO.GetFromClipboard

'Get the text from the DataObject

sText = oDO.GetText

'If we know the text is in a US format,

'use Val() to convert it to a number

ActiveCell.Value = Val(sText)

End

Sub

0 0

Post a comment