Issue when copypasting from excel to notepad++
-
Hello guys!
For a while I’ve been having this weird situation. When I copy something in Excel and then paste it in notepad++, a quote is added at the begining of the line, and at the end of it.
For example:
If I copy from excel
hello world
and then paste it to notepad++ i get:
“hello world”If I copy from excel
hellooooo
world
and then paste it to notepad++ i get:
“hellooooo”
“world”Could you please help me remove all those extra quotes?
I can’t use replace function, as I use a lot of quotes and it only helps when I get double quotes but it’s very annoying to have to delete every other extra quote line by line.Thank you very much.
-
If you just want to get rid of all the quotes (and assuming they are real quotes, not the smart quotes that are seen in the forum), just do a search and replace: find what=
", replace with=If you only want to get rid of matched quote pairs, then enable search mode =
regular expression, and set find what="([^"]*)"and replace with=$1. (edit: to clarify, the quote marks around the parenthesis are part of the search-string, so you should type those, too)If you have some places where you need to keep quote marks, you’ll have to give better representation of the data.
-----
FYI: if you have further search-and-replace (regex) needs, study this FAQ and the documentation it points to.Before asking another regex question, understand that (since you’ve been informed now), for future requests, many of us will expect you to show what data you have (exactly), what data you want (exactly), what regex you already tried (to show that you’re showing effort), why you thought that regex would work (to prove it wasn’t just something randomly typed), and what data you’re getting with an explanation of why that result is wrong. When you show that effort, you’ll see us bend over backward to get things working for you. If you need help formatting the data so that the forum doesn’t mangle it (so that it shows “exactly”, as I said earlier), see this help-with-markdown post, where @Scott-Sumner gives a great summary of how to use Markdown for this forum’s needs.
Please note that for all “regex” queries – or queries where you want help “matching” or “marking” or “bookmarking” a certain pattern, which amounts to the same thing – it is best if you are explicit about what needs to match, and what shouldn’t match, and have multiple examples of both in your example dataset. Often, what shouldn’t match helps define the regular expression as much or more than what should match.
-
i can not reproduce the quotes when copying from excel 2010 and pasting to notepad++ 7.6.2.
the content is copied as plain text and cells are separated by a tab.
if quotes exists in a cell, they will be copied, but no further quotes are added.here is a screenshot:
which excel version do you use ?
does your excel sheet have any special layout or formatting ? -
Excel often (but not always) gives double-quotes surrounding text entries when copying from Excel to the clipboard and a text-based media. I don’t know the exact circumstances, but it’s not unusual.
-
Okay, I was just able to force it by setting A1 =
"hello"&char(9)&"world", so there’s a tab character: when I copied from Excel 2016 to NPP, it surrounded it by quotes and pasted"hello world"in NPP(where that center character is a TAB)Also surrounds by quotes if there’s a newline
-
this superuser.com post shows that it’s an issue with Excel, not with the destination text editor: same happens whether you are pasting into Notepad++ or windows notepad or gvim or whatever: Excel actually puts the quotes in the PLAIN_TEXT version in the clipboard. (I forget the clipboard nomenclature, but when you copy from Excel, it puts multiple copies of the selection into the clipboard under various windows API clipboard-constants; when another application grabs from the clipboard, it selects the most appropriate clipboard-constant to paste; the quotes are actually in the plain-text version in the windows clipboard)
edit: ahh, based on another post, it’s because Excel outputs valid tab-separated CSV when going to the plain-text in the clipboard: it uses tabs to separate cells, and newlines to separate rows, so if you embed a tab or a newline in your excel cell, it has to put quotes around the cell value to be valid tab-separated CSV.
-
thanks @PeterJones , now i can reproduce.
(i barely use excel so i didn’t even now that you can put a tab or a new line in a cell ;-) )now i definitively second your first suggestion using regex to replace the pairs of quotes:
If you only want to get rid of matched quote pairs, then enable search mode = regular expression, and set find what=“([^”]*)" and replace with=$1. (edit: to clarify, the quote marks around the parenthesis are part of the search-string, so you should type those, too)
