Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Issue when copypasting from excel to notepad++

    Help wanted · · · – – – · · ·
    3
    7
    3747
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Joselyn Bruna
      Joselyn Bruna last edited by

      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.

      Meta Chuh 1 Reply Last reply Reply Quote 0
      • PeterJones
        PeterJones last edited by PeterJones

        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= (empty replace)

        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.

        1 Reply Last reply Reply Quote 2
        • Meta Chuh
          Meta Chuh @Joselyn Bruna last edited by Meta Chuh

          @Joselyn-Bruna

          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:
          Imgur

          which excel version do you use ?
          does your excel sheet have any special layout or formatting ?

          1 Reply Last reply Reply Quote 0
          • PeterJones
            PeterJones last edited by

            @Meta-Chuh,

            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.

            1 Reply Last reply Reply Quote 0
            • PeterJones
              PeterJones last edited by PeterJones

              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

              1 Reply Last reply Reply Quote 1
              • PeterJones
                PeterJones last edited by PeterJones

                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.

                Meta Chuh 1 Reply Last reply Reply Quote 3
                • Meta Chuh
                  Meta Chuh @PeterJones last edited by

                  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)

                  1 Reply Last reply Reply Quote 1
                  • First post
                    Last post
                  Copyright © 2014 NodeBB Forums | Contributors