In Excel, I have a large 'raw' data sheet with text cells and another sheet that shows a subset of the raw data organized differently, using 'indirect' formulas - e.g., =INDIRECT("Data!C5"), showing the content of C5 in Data sheet (e.g., "I like dogs").

Now, while looking at such a 'derived' sheet, I want to (manually) copy part of the text that appears in a particular cell (e.g., just the word 'dogs' in the above example), and paste it somewhere else. Pressing F2 hides the referred text cell, and shows the formula (as expected) - which is not what I need.

Is there an easy way to do it? I may potentially need to do it for tens or even hundreds of cells - so, solutions like copying the entire cell content to Notepad and copy-pasting from there would be too much of an overhead.

  • Is there a reason you're using INDIRECT(), instead of just, e.g. =Data!C5? – Bandersnatch Mar 30 '18 at 16:34
  • @Bandersnatch yes, in reality this is a formula - e.g., indirect($b2 & "!" & $h10 & row()) – Alex Glikson Mar 31 '18 at 1:40

One possible approach is to do the following:

  1. copy the entire ('derived') sheet to a new sheet
  2. switch to the new sheet, select all the cells (with 'indirect' formulas), press 'copy'
  3. paste them 'in place' (i.e., over the same range of cells) as 'values'
  4. work with the new sheet (which is plain text now) instead of the original one

Of course, if the 'raw' data sheet changes, these changes will not be reflected in the new sheet. But the selective copy-pasting should be easy enough now.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.