19

I can replace a formula in one cell by its calculated value by selecting the cell, hitting F2 then F9 and Enter.

When I want to do this for every cells in a column, and height of that column == N, then the repeating of this job N times is not happy for me.

Is that way to do this for an entire column at once?

Example input:

    1
A   =CONCATENATE("something1","something2";)
B   =CONCATENATE("something3","something4";)
...
n   =CONCATENATE("somethingN","somethingN+1";)

Desired output:

    1
A   something1something2
B   something3something4
...
n   somethingNsomethingN+1
moggi
  • 1,466
  • 4
  • 18
  • 29
user3766478
  • 641
  • 2
  • 7
  • 16
  • I see you have edited your question, but wonder if you have tried my answer, which I would have thought solved your problem. Have you had a look at it? I see no comment or vote. – PJTraill Sep 20 '17 at 08:45

3 Answers3

35

I know it's old, but just for anybody searching for the correct answer:

Data > Calculate > Formula to Value

No clipboard voodoo necessary ;)

runlevel0
  • 2,715
  • 2
  • 24
  • 31
7

This question sounds as if it would fit better on SuperUser.com.

According to https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=28242 you can Copy the column to the clibboard, then use Edit / Paste Special with Formulas unchecked and Values checked. However that answer is from March 2010, and I now (June 2017, Libre Office 5.2) see no Values in the dialogue.

According to https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=44313 (from 2011) you should keep Numbers checked.

I did it in LibreOffice 5.2 with Formulas unchecked and Numbers, Text and Date/Time checked (after all, what if the result is not a number?) and it worked in my particular case.

PJTraill
  • 1,353
  • 12
  • 30
  • This worked for me after I also found the instruction to check "values" in paste special, but there were no "values" to check. – Betty Mock Apr 29 '19 at 02:20
1

Cut and paste the cells into a pain text editor such as Gedit, then copy them back over where they came from in the spreadsheet. The copy operation carries the values not the formulas.

This will work with Gnumeric as well, of course - which is convenient, cos can't find any other way of doing it in Gnumeric.

markling
  • 1,232
  • 1
  • 15
  • 28
  • 1
    A pity you don't have any vote, as this is usually my favorite solution to anything itchy ;) – JPT Apr 13 '23 at 15:43