remove quotation from excel while pasting the data

[anee_4285]

Hi All,

When ever i'm pasting data from Excel to TXT i'm getting quotation mark (") for each cell. How to remove them. Remember i have copy lot of cell's so i can't use F2 and copy data and paste.

 

[Michael M]

Can't you do a Find /Replace
In the find box, put the quotes and leave the Replace box blank...then replace ALL

 

[anee_4285]

HI,

No. actually i'm not pasting in txt file. Its my Network device. In this there is no option of replace. So every time i have to paste it in txt then replace it then paste it in my device. I have around 250 devices and 250 different data from excel. So it will increase my work

I have copy from Excel then paste it in txt file. replace the words then copy it from excel and then paste it in my device. So for one format it will take lot of time. If i get the solution of not getting the quotation then i will copy and paste it in my network device directly.

 

[Akuini]

In my experience, when I copy paste from excel to a text file, it will add quotation marks if the cell has multiple lines. Is that what happens with your data?

I think you will need vba code to solve the issue, so basically you'll need to:
1. select the range to copy
2. run the code (it will remove all quotation marks in the clipboard)
3. open the text file (or your Network device?)
4. paste

Note:
I assumed:
1. The data is in a single column
2. There are no quotation marks in the data

Let me know if you're interested in this method.

 

[anee_4285]

In my experience, when I copy paste from excel to a text file, it will add quotation marks if the cell has multiple lines. Is that what happens with your data?

I think you will need vba code to solve the issue, so basically you'll need to:
1. select the range to copy
2. run the code (it will remove all quotation marks in the clipboard)
3. open the text file (or your Network device?)
4. paste

Note:
I assumed:
1. The data is in a single column
2. There are no quotation marks in the data

Let me know if you're interested in this method.

Yes i'm interested.

 

[Akuini]

OK, try this:

[FONT=Lucida Console][color=Royalblue]Sub[/color] a1112871a()
'https://www.mrexcel.com/forum/excel-questions/[color=Brown]1112871[/color]-remove-quotation-excel-while-pasting-data.html
    [color=Royalblue]Dim[/color] obj [color=Royalblue]As[/color] [color=Royalblue]New[/color] DataObject
    [color=Royalblue]Dim[/color] tx [color=Royalblue]As[/color] [color=Royalblue]String[/color]

    Selection.Copy
    
    obj.GetFromClipboard
    tx = obj.GetText
    
    Application.CutCopyMode = False
    tx = Replace(tx, Chr([color=Brown]34[/color]), [color=Darkcyan]""[/color])
    
        obj.SetText tx
        obj.PutInClipboard

    MsgBox [color=Darkcyan]"Data has been copied to clipboard"[/color]


[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

If the code works for you then you can assign it to keyboard shortcut so it will be easier to run.

 

[Akuini]

I forgot something:
You need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)

And this is how:
1. Open your VBA editor.
2. Click Tools > References.
3. Check the box next to “Microsoft Forms 2.0 Object Library.”

 

[encryptings]

Using VBA code should work, but as Michael M. above said. Using Notepad as an intermediary within less than a second on small file size you could replace all quote marks. Then CTRL-A, CTRL-C and paste directly into your Network device.