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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Can't you do a Find /Replace
In the find box, put the quotes and leave the Replace box blank...then replace ALL
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.”
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top