0

I want to show a cell value in a cell where it is being referenced to a cell that includes a simple formula. e.g.

In D4 I want to reference C4 (i.e. =C4) and show it as a value, where C4 has a concatenate formula: =concatenate("000",A4,"-",E4,"-",J4,"-",M4).

Explanation

I currently have a reference in D4 cell that is C4, which returns a value, in D4, of 0001-190716-AM-ABERD, for example. When I double click into D4 in order to copy the text as text, I simply get the formula i.e. =C4. What I'm trying to figure out is if there is a way that I can put a formula into D4 that not only returns the value 0001-190716-AM-ABERD, but returns it as text - so when I double click into the cell the text shows and not the formula?

  • 1
    I don't understand "show it as a value" means. If the result of formula is 000-222-111, then this is what will be seen in both C4 and D4, provided you have =C4 in the D4 cell. – user79865 Jul 29 '16 at 17:09
  • I currently have a reference in D4 cell that is =C4, which returns a value, in D4, of 0001-190716-AM-ABERD, for example. When I double click into D4 in order to copy the text as text, I simply get the formula i.e. =C4. What I'm trying to figure out is if there is a way that I can put a formula into D4 that not only returns the value 0001-190716-AM-ABERD, but returns it as text - so when I double click into the cell the text shows and not the formula? – Gary Willmott Aug 1 '16 at 8:23
  • This can't be done. Just don't double click. Click once to select a cell, and copy. – user79865 Aug 1 '16 at 11:35
  • I owndered if that was the case. Thanks for your help. – Gary Willmott Aug 1 '16 at 15:36
2

If a user double-clicks in a cell, what they see is their input into that cell. In the situation described, that input will be a formula, for example =C4, not the output of that formula such as "0001-190716-AM-ABERD".

To copy the output of a formula, select the cell by clicking on it once, then copy. When pasting the result in another place in the spreadsheet, you have two options:

  • ordinary paste Ctrl-V will copy the formula, adjusting its cell references
  • value-only paste Ctrl-Shift-V will copy only the output of the formula, as plain text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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