Paste Special no longer replaces blank formulas in Calc Spreadsheet 24.8

English
#1

I’ve been using LibreOffice Calc Spreadsheet for years. I often copy a range of cells and use Paste Special > Paste Special > Values & Formats (Formula option is NOT selected) to replace formulas with their values. In that case, cells with formulas but no values [for example: =if(A1="","",A1+A2) where A1 is blank] have always removed the formula in the cell.

The most recent versions (24.8.0 and 24.8.2) do not remove the formula.

I just reverted to 24.2.6.2 and it works as I expected, removing the formula.

Am I missing something? Has something changed in the way Calc Spreadsheet’s Paste Special function works in 24.8? Or is there possibly a bug in 24.8?

Thank you.

#2

That if do you paste over the same range.
This seem a bug, because if Numbers, Text and Formats are selected, the OK button don’t remove the formula. The same with Ctrl+Alt+Shift+V.

Version: 24.8.2.1 (X86_64) / LibreOffice Community
Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13
CPU threads: 4; OS: Windows 10 X86_64 (10.0 build 17763); UI render: Skia/Raster; VCL: win
Locale: es-AR (es_AR); UI: es-ES
Calc: threaded

#7

Having read the (first) 2 Answers:
With LibO V 24.8.2.1 under Win 10 I couldn’t reproduce the reported behaviour.
This was independent of where I “pasted special” (also “in situ” / overwriting the source).
Did I misundertstand an explanation?
First time that I insert an image:
grafik
In no case a formula from the source was pasted.

#8

Maybe not.
Test to copy and paste B1:B4 of the following file.
formula to no blank.ods (8.8 KB)

#9

Perhaps this is OS specific? I’m running macOS Sequoia.

However, to use the file:
formula to no blank.ods
to test this issue, you need to change the formula in cell B2 to
=IF(C2="","",A2)
Then copy A1:B3 and paste special.

When I use the revised testing file to test this in version 24.2.6, the formula in cell B2 is no longer there. The cell becomes blank after using Paste Special.
When I use the revised testing file to test this in version 24.8.2, the formula remains in cell B2 after using Paste Special.

I personally prefer the way 24.2.6 handles this, and I appreciate that karolus provided me with a workaround I can use in 24.8.2 to get rid of the formulas I no longer need:
Data > Calculate > Formula to Value

Thanks.

#10

Did so (Paste Special… no formulas). Worked as expected. The target cells got only the results. The cell taking special pasted things from B4 is blank. This differs from the effect I get for B4 with
>Data>Calculate>Formula to Value (cell claiming to be text, not blank, LEN(B4)=0).
(Still V24.8.2.1 on Win 10)
See also:
disask111648_responding_to_comment8.ods (191.8 KB)

#11

With the Values & Formats button, or with the OK button?

#12

Hope this can avoid doubts:
disask111648_responding_to_comment8_V2.ods (21.6 KB)
Better?:
disask111648_responding_to_comment8_V3.ods (24.3 KB)

#13

In the formula to no blank.ods sample file:

  • Copy B1:B4
  • In the same range, Ctrl+Shift+V (or menu Edit - Paste Special - Paste Special)
  • Values & Formats button:
    imagen
    Results:
    imagen

imagen

#14

Yes, what you are seeing with version 7.6.7.2 is what I’ve seen with every version I’ve been using for the past several years, up to versions 24.2.6 and 24.8.0. It changed beginning with version 24.8.1.

#15

FYI, this is fixed in v24.8.4.2.
(It was still broken in 24.8.2. It may or may not have been fixed in 24.8.3, but I missed that update, so didn’t test.)

#3

Thank you for your reply, LeroyG. I’m editing my previous reply to you asking if you thought it was a bug. I new to using this forum and read only your reply in the gray box, but now see that you also said that it seems to be a bug. Thanks again.

#4

Hallo
For replacing the formulas with there value inplace you may use:
⇒ Data ⇒ calculate ⇒ Formula to Value instead.

1 Like
#5

Thank you, karolus

#6

And menu Data - Text to Columns - OK.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.