repost: paste special splits clipboard data using comma as delimiter -- is this a mode setting?

[phil]

I use this VBA code:

Here is the information from the earlier post

activesheet.pastespecial format:="text"

to copy in something that I have pasted in from a website using the
clipboard. This has worked forever (and still does on another copy of Excel
that I have installed on a different machine). All of a sudden, a value such
as

$125,341

gets copied into 2 adjacent cells and looks like this

$125 341

Needless to say, the VB code that processes this new data result no longer
works. The same thing happens if I use "paste special" from Excel directly,
rather than using VBA.

This seems like one of those mode switches that you can accidentally set the
wrong way without realizing it.

How do I get Excel to copy the above data into one cell again?

 

[Bernie Deitrick]

Phil,

I don't know why you are getting that different behavior, but if you want to
paste into a single cell, simply use F2 prior to pasting to enter cell edit
mode.

HTH,
Bernie
MS Excel MVP

 

[phil]

Thanx for the reply, but unfortunately that did not help.

A workaround that I have used for my particular situation is to use
format=HTML and an option that ignores all of the links etc. Using the HTML
format, the pastespecial function does not see "," (comma) as a delimiter in
the clipboard data and does the right thing.

However, I am curious because there are other situations where pasting
results into a worksheet treats the presence of a comma as delimiter and it
is annoying. Particularly because I have not seen the behavior in the past
(I have been running the code outlined below hundreds of times without
problems on my current machine) and this problem does not happen on the
exact same version of Excel on a different computer.

I don't believe that there is a software bug somewhere else in my code
(usually the case when people report this kind of anomalous behavior)
because I get exactly the same behavior when I use paste special directly
from the pulldown menu in Excel.

This is why it seems like an obscure mode setting issue.

thanx again.

Phil