Ask Your Question

paste special formula not pasting as number only text

asked 2018-02-15 16:32:35 +0200

Luddite Savant gravatar image

updated 2018-02-15 16:34:10 +0200

I'm trying to work with some financial data in Libreoffice (v4.2.8.2) but am running into some issues in how it handles text/number cells. I have cells, formatted as number, which contains USD after the numbers so to strip that out I use the formula =LEFT(A1, 5).

This produces the figures I want to manipulate, but the cell is a formula so I paste only/paste special as number, but the cell I paste into remains empty. The only way I'm able to get anything to appear is by pasting as text, which then gives me the annoying apostrophe before the numbers that I have to remove before I can manipulate the numbers.

I know how to do the find/replace solution to get rid of the apostrophe but what I want to do is to find a way to be able to paste the output of my formula as a number, so I can then do calculations with it direct.

If it helps the original data I'm using is imported from .csv but is Unicode (UFT-8). I've tried with & without 'Detect Special Numbers' and have used both UK & USA English as the language. I've checked that all the cells I'm dealing with are formatted as numbers but it doesn't make any difference. I can paste a number as a number but it won't paste the output of a formula as a number.

I'm sure someone must have come across this before, and it's likely a novice mistake in my settings but I've not managed to figure out how to fix it & be able to paste the output of my formula as a number. I'd appreciate any help you can give.


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-02-15 20:51:39 +0200

=VALUE(LEFT(A1; 5)); then use Data-Calculate-Formula to Value to avoid copy+Paste.

edit flag offensive delete link more


Thanks Mike, I can't see Calculate or Formula to Value in the Data menu (or any others) so will have continue copy/pasting but =VALUE works a treat.

Thanks for the help

Luddite Savant gravatar imageLuddite Savant ( 2018-02-15 21:28:29 +0200 )edit

Oh! My bad. I had overlooked the version info... so ancient version, of course.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-15 21:38:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-15 16:32:35 +0200

Seen: 315 times

Last updated: Feb 15 '18