How to stop Excel adding quotation marks when copying to notepad?

[AI32768]

I'm just interested in stopping Excel adding quotation marks around text
pasted from copied cells into notepad without having to go into notepad and
do endless 'find and replace's afterwards.

Formatting the cells as text makes no difference so why is Excel adding the
quotes in only around cells containing IP addresses? (And putting a tab after
the first quote marks!)

[Dave Peterson]

I think it depends on what's in your cells.

You could take a macro approach.

Saved from a previous post:

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Sub

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

=========

If you wanted to copy multiple cells, this may help you:

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myCell As Range
Dim myRow As Range
Dim myRng As Range
Dim myRowStr As String
Dim myStr As String

Set MyDataObj = New DataObject

Set myRng = Selection.Areas(1)

myStr = ""
For Each myRow In myRng.Rows
myRowStr = ""
For Each myCell In myRow.Cells
myRowStr = myRowStr & vbTab & myCell.Text
Next myCell
myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
myStr = myStr & vbCrLf & myRowStr
Next myRow
myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

--

Dave Peterson

[bste...@gmail.com]

On Monday, October 26, 2009 8:19:01 AM UTC-4, AI32768 wrote:
> I'm just interested in stopping Excel adding quotation marks around text

> pasted from copied cells into notepad without having to go into notepad and

> do endless 'find and replace's afterwards.

>
> Formatting the cells as text makes no difference so why is Excel adding the
> quotes in only around cells containing IP addresses? (And putting a tab after
> the first quote marks!)

I was able to get around this issue by highlighting all of my text in Excel, Copying it, then open a new Wordpad document. In Wordpad go to Edit menu and select "Paste Special" then choose "Unformatted Text" and hit OK. Next save your Wordpad document as a text file. No more extra quotes.

[GS]

bste...@gmail.com pretended :

Perhaps Excel thinks you're trying to create a tab delimited text file?

It would be much, much faster/easier/better to dump the data into an
array and write the data directly to a text file using standard VB file
I/O functions. No fuss, no muss, just pure data dumped into a plain
text file!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[ja...@isnottv.com]

Finally found a solution for this....!!

If you publish as web, and copy from, there (instead of from the GSheet directly), then it doesn't add the double quotation mark

Not ideal, but does the trick ;p

[marcus....@gmail.com]

In my case it was caused by having a carriage return (CR) in one of the cells that was forming the text string. This resulted in excel adding the double quotes around the string. When I removed the CR then Excel no longer added the additional double quotes.

[Luca De Giglio]

double click the cell and just copy the content.
In other words:
- if you select the CELL you get the annoying quotation marks.
- if you select the CONTENT of the cell, you don't get them.