Copy & Paste issues with "Quotes"

[Jake Bailey]

Hello All -
I'm having an issue when I copy data from my POS software and then
paste it into Excel. I believe the issue revolves around the use of "
in certain fields.

For example, one of the lines that I'm having problems with looks like
this when I copy it to Notepad:

"AVN12223" "Pipe Extension - 3"" 336.40"
"Accessory" "9" "Each"

When I copy the same data on the clipboard to Excel it comes out like
this

AVN12223 Pipe Extension - 3"336.40Accessory
9 Each

As you can tell the second cell has more data in it then it should -
'Pipe Extension - 3"336.40Accessory'
It's apparent that the " behind the 3 is messing this process up.
Unfortunately I have a few hundred records in my system that end in
quotes that cannot be changed.

I've tried formatting the sheet as text first but that doesn't help me
out.

Any ideas on how I can work around this?

Thanks,
Jake

 

[macropod]

Hi Jake,

If you first paste the whole string into Column A, then:
.. use Find/Replace to replace all double spaces with a single space
.. use Find/Replace to replace all single spaces followed by a double-quote with a double-quote
.. use Text-toColumns, with a double-quote field delimiter, treating multiple delimiters as one
your data will be correctly parsed - starting in Column B.

 

[Jake Bailey]

Thanks for the quick reply. Unfortunately when I copy the data into
Column A it copies all of the data to the entire sheet, ie; Column A,
B, C, and so on. Is there something I'm doing wrong?

 

[macropod]

Hi Jake,

That's probably happening because Excel is 'remembering' the parsing you've already been doing. I think that closing & reopening the
workbook, or doing the work on a different worksheet, will fix that.

 

[Joerg Mochikun]

Hmmm...no problems here (Excel2003). Have you tried Edit>Paste Special>Text?
JM

 

[macropod]

Hi Joerg,

The phenomenum reported by Jake occurs when you paste data onto a worksheet on which the Text-to-Columns function has already been
used for previous data.