Ask Your Question

paste special formula not pasting as number only text [closed]

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

Luddite Savant gravatar image

updated 2020-07-09 11:55:07 +0200

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-30 14:52:03.751652

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

Question Tools

1 follower


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

Seen: 883 times

Last updated: Feb 15 '18