### paste special formula not pasting as number only text

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 set of numbers 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.

Thanks