Copy a cell and paste its value

Discuss the spreadsheet application
Post Reply
tbrantly
Posts: 6
Joined: Sun Feb 21, 2010 5:30 pm

Copy a cell and paste its value

Post by tbrantly »

How do I remove the formula from a cell and replace it with the value the formula calculated?

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
Tom Brantly
OpenOffice 3.1 on Windows XP
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Problem with spreadsheet

Post by Robert Tucker »

Why would you want to do that?
LibreOffice 7.x.x on Arch and Fedora.
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Problem with spreadsheet

Post by Hagar Delest »

You mean the equivalent of the Paste>Value in MS Excel?

Try the long click on the paste icon (or click its little arrow) and use Unformatted text.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
vasa1
Volunteer
Posts: 261
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: Problem with spreadsheet

Post by vasa1 »

Hagar de l'Est wrote:You mean the equivalent of the Paste>Value in MS Excel?

Try the long click on the paste icon (or click its little arrow) and use Unformatted text.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
I searched for the keyboard equivalent but couldn't find it. Is there one? I've been using the more cumbersome "Alt, edit, paste special", and then changing the defaults to exclude "paste all", "paste formulas" and "paste formats", but to include numbers. Of course, this has to be done only once per session. After the first time I set it, it is just Alt, E, S, enter, enter.

And Robert Tucker asked, "Why would you want to do that?". Well, in my case, I don't need the formula that generated the value in a temp worksheet any more.
LibreOffice 5.2.1.2 on Lubuntu 16.04 (Openbox)
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Problem with spreadsheet

Post by Hagar Delest »

vasa1 wrote:I searched for the keyboard equivalent but couldn't find it. Is there one?
No idea, I usually prefer to use the buttons instead of the keyboard.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
vasa1
Volunteer
Posts: 261
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: Problem with spreadsheet

Post by vasa1 »

Hagar de l'Est wrote:
vasa1 wrote:I searched for the keyboard equivalent but couldn't find it. Is there one?
No idea, I usually prefer to use the buttons instead of the keyboard.
That's okay! Gave me a chance to record my third ever macro :D . So now, Alt+V will do it for me.
LibreOffice 5.2.1.2 on Lubuntu 16.04 (Openbox)
vasa1
Volunteer
Posts: 261
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: Copy a cell and paste its value

Post by vasa1 »

This is what the macro I recorded looks like:

Code: Select all

sub MyPaste
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SelectedFormat"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:ClipboardFormatItems", "", 0, args1())


end sub
I assigned Alt+V to it. If it's just one cell or just one row, pressing Alt+V pastes the unformatted contents, but if it's more than one row, I have to hit enter after Alt+V to complete the paste.
LibreOffice 5.2.1.2 on Lubuntu 16.04 (Openbox)
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Copy a cell and paste its value

Post by acknak »

This feature was added to OOo 3.2; you don't need a macro to do it.

There is a "paste unformatted" function, bound to Ctrl+Alt+Shift+V by default; you can bind a different key combination using the Tools > Customize dialog window.

Oops--Sorry. It works in Writer, but apparently not in Calc. My understanding is that the new function was supposed to apply to all the OOo applications, but maybe I missed something.
AOO4/LO5 • Linux • Fedora 23
vasa1
Volunteer
Posts: 261
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: Copy a cell and paste its value

Post by vasa1 »

acknak wrote:This feature was added to OOo 3.2; you don't need a macro to do it.

There is a "paste unformatted" function, bound to Ctrl+Alt+Shift+V by default; you can bind a different key combination using the Tools > Customize dialog window.

Oops--Sorry. It works in Writer, but apparently not in Calc. My understanding is that the new function was supposed to apply to all the OOo applications, but maybe I missed something.
I had the same experience. After making the macro, I too read about this "Ctrl+Alt+Shift+V" but didn't get it to work in Calc.
LibreOffice 5.2.1.2 on Lubuntu 16.04 (Openbox)
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Copy a cell and paste its value

Post by Hagar Delest »

vasa1 wrote:didn't get it to work in Calc.
+1 with OOo 3.2 and xubuntu 9.10. No shortcut at all linked to that shortcut.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
davesolomon
Posts: 1
Joined: Thu Jun 27, 2019 9:13 am

Re: Copy a cell and paste its value

Post by davesolomon »

The new version I am using of OpenOffice allows me to paste the value only on a mac by using the Command-Shift V when pasting the copied cell.
DaveSolomon Apache OpenOffice 4.1.6
Post Reply