Text to columns on pasteHow do I change the delimiter when copy-pasting from Excel?Excel 2010 Macro - Copy...

Basic combinations logic doubt in probability

Can you tell me why doing scalar multiplication of a point on a Elliptic curve over a finite field gets to a point at infinity?

Why are 150k or 200k jobs considered good when there are 300k+ births a month?

Motorized valve interfering with button?

How long does it take to type this?

N.B. ligature in Latex

Is it legal for company to use my work email to pretend I still work there?

Writing rule which states that two causes for the same superpower is bad writing

How is it possible to have an ability score that is less than 3?

How to feed LSTM with different input array sizes?

The magic money tree problem

What defenses are there against being summoned by the Gate spell?

Pronouncing Dictionary.com's W.O.D "vade mecum" in English

"You are your self first supporter", a more proper way to say it

Why Is Death Allowed In the Matrix?

Mathematical cryptic clues

XeLaTeX and pdfLaTeX ignore hyphenation

Suffixes -unt and -ut-

Why don't electron-positron collisions release infinite energy?

Is there any sparring that doesn't involve punches to the head?

How to report a triplet of septets in NMR tabulation?

How can bays and straits be determined in a procedurally generated map?

DOS, create pipe for stdin/stdout of command.com(or 4dos.com) in C or Batch?

"which" command doesn't work / path of Safari?



Text to columns on paste


How do I change the delimiter when copy-pasting from Excel?Excel 2010 Macro - Copy cell range with formulas and paste values onlyExcel copy and paste selective columnsPaste in Excel without splitting on newlinesSplitting excel sheet into multiple sheets based on two columnsIssues unlocking sheets in workbookHide & Unhide macros won't work after locking columns & protecting sheetMicrosoft Excel 2013 - cannot delete columns no matter what I try**Text to Columns** for binary code in ExcelVBA code to protect sheets and show/hide buttons






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















The Goal



First of all, here's what I'm attempting to do. I have a sheet which has some editable columns say, A1-A10. Cells A11-A20 are all calculated cells and I want them hidden and protected. When I paste in data from my external source, it comes in as CSV. When I paste in data into my protected sheets, I want this data to split on comma across the available cells A1-A10.



The Problem



With the sheet protected, TextToColumns is disabled, and thus pasting in my data doesn't split on my comma delimiter because the default delimiter is a tab.



What I've Tried




  1. I've attempted to use the VBA function TextToColumns, but that is not allowed on protected sheets

  2. I've created a button on an existing unprotected "readme" sheet that would set the delimiter to comma and perform the split on a dummy cell hoping that this would carry over using the comma as a delimiter when pasting data into subsequent pages, but this didn't work. The pasted text came in as the full csv line, not separated.

  3. I've manually performed the TextToColumns trick in #2 by clicking the TextToColumns button in the ribbon bar. This does work, but I'd rather not have to do this manual step every time.


The Question



Is there an easy way to do this that I'm missing? Can I set my default delimiter in Excel to be a comma? Looking for some advice on this, or worst case scenario, am I stuck doing the manual process in step 3?



Note



Why can't I just unprotect the sheet? I want it protected so that if I accidentally grab too many columns from my source and paste it into the sheet, it doesn't overwrite any of my calculation fields.










share|improve this question





























    1















    The Goal



    First of all, here's what I'm attempting to do. I have a sheet which has some editable columns say, A1-A10. Cells A11-A20 are all calculated cells and I want them hidden and protected. When I paste in data from my external source, it comes in as CSV. When I paste in data into my protected sheets, I want this data to split on comma across the available cells A1-A10.



    The Problem



    With the sheet protected, TextToColumns is disabled, and thus pasting in my data doesn't split on my comma delimiter because the default delimiter is a tab.



    What I've Tried




    1. I've attempted to use the VBA function TextToColumns, but that is not allowed on protected sheets

    2. I've created a button on an existing unprotected "readme" sheet that would set the delimiter to comma and perform the split on a dummy cell hoping that this would carry over using the comma as a delimiter when pasting data into subsequent pages, but this didn't work. The pasted text came in as the full csv line, not separated.

    3. I've manually performed the TextToColumns trick in #2 by clicking the TextToColumns button in the ribbon bar. This does work, but I'd rather not have to do this manual step every time.


    The Question



    Is there an easy way to do this that I'm missing? Can I set my default delimiter in Excel to be a comma? Looking for some advice on this, or worst case scenario, am I stuck doing the manual process in step 3?



    Note



    Why can't I just unprotect the sheet? I want it protected so that if I accidentally grab too many columns from my source and paste it into the sheet, it doesn't overwrite any of my calculation fields.










    share|improve this question

























      1












      1








      1








      The Goal



      First of all, here's what I'm attempting to do. I have a sheet which has some editable columns say, A1-A10. Cells A11-A20 are all calculated cells and I want them hidden and protected. When I paste in data from my external source, it comes in as CSV. When I paste in data into my protected sheets, I want this data to split on comma across the available cells A1-A10.



      The Problem



      With the sheet protected, TextToColumns is disabled, and thus pasting in my data doesn't split on my comma delimiter because the default delimiter is a tab.



      What I've Tried




      1. I've attempted to use the VBA function TextToColumns, but that is not allowed on protected sheets

      2. I've created a button on an existing unprotected "readme" sheet that would set the delimiter to comma and perform the split on a dummy cell hoping that this would carry over using the comma as a delimiter when pasting data into subsequent pages, but this didn't work. The pasted text came in as the full csv line, not separated.

      3. I've manually performed the TextToColumns trick in #2 by clicking the TextToColumns button in the ribbon bar. This does work, but I'd rather not have to do this manual step every time.


      The Question



      Is there an easy way to do this that I'm missing? Can I set my default delimiter in Excel to be a comma? Looking for some advice on this, or worst case scenario, am I stuck doing the manual process in step 3?



      Note



      Why can't I just unprotect the sheet? I want it protected so that if I accidentally grab too many columns from my source and paste it into the sheet, it doesn't overwrite any of my calculation fields.










      share|improve this question














      The Goal



      First of all, here's what I'm attempting to do. I have a sheet which has some editable columns say, A1-A10. Cells A11-A20 are all calculated cells and I want them hidden and protected. When I paste in data from my external source, it comes in as CSV. When I paste in data into my protected sheets, I want this data to split on comma across the available cells A1-A10.



      The Problem



      With the sheet protected, TextToColumns is disabled, and thus pasting in my data doesn't split on my comma delimiter because the default delimiter is a tab.



      What I've Tried




      1. I've attempted to use the VBA function TextToColumns, but that is not allowed on protected sheets

      2. I've created a button on an existing unprotected "readme" sheet that would set the delimiter to comma and perform the split on a dummy cell hoping that this would carry over using the comma as a delimiter when pasting data into subsequent pages, but this didn't work. The pasted text came in as the full csv line, not separated.

      3. I've manually performed the TextToColumns trick in #2 by clicking the TextToColumns button in the ribbon bar. This does work, but I'd rather not have to do this manual step every time.


      The Question



      Is there an easy way to do this that I'm missing? Can I set my default delimiter in Excel to be a comma? Looking for some advice on this, or worst case scenario, am I stuck doing the manual process in step 3?



      Note



      Why can't I just unprotect the sheet? I want it protected so that if I accidentally grab too many columns from my source and paste it into the sheet, it doesn't overwrite any of my calculation fields.







      microsoft-excel






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 2 days ago









      tjanstjans

      1084




      1084






















          0






          active

          oldest

          votes












          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "3"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1421967%2ftext-to-columns-on-paste%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Super User!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1421967%2ftext-to-columns-on-paste%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          VNC viewer RFB protocol error: bad desktop size 0x0I Cannot Type the Key 'd' (lowercase) in VNC Viewer...

          Couldn't open a raw socket. Error: Permission denied (13) (nmap)Is it possible to run networking commands...

          Why not use the yoke to control yaw, as well as pitch and roll? Announcing the arrival of...