Google-sheets – How to show cell data as a value when referencing another cell that includes a formula

google sheets

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?

Best Answer

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.
Related Topic