remove quotation from excel while pasting the data

anee_4285

New Member
Joined
Jul 9, 2015
Messages
27
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,205
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 9, 2015
Messages
27
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 9, 2015
Messages
27
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
OK, try this:

Code:
[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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
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

New Member
Joined
Oct 20, 2019
Messages
1
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.
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top