Currency conversion/display in calc

users
#1

Hi.
If I type or paste $2,441.99 into a cell in calc (3.6) it is left aligned. If I right click format cell it states number-general.
If I divide it by 2 I get #value!

How do I get LO to recognise this as a number. I found a long winded way, format it as currency and remove the ' that LO puts in front.
Steve

#2

Hi :slight_smile:
I think the usual way is to avoid typing in the $ sign and just let the cell formatting put that in front for you. 
Regards from
Tom :slight_smile:

#3

Did it again… sent it privately. Sorry again, then. Here's to the list:

#4

Same mistake as usual, sending my replies privately. I will never
learn, I'm quite sure of that now…

Anyway, here it is again, to the list this time:

#5

Hi :slight_smile:
I think you might be over-complicating it?  Just typign the number in as a number and letting the formatting do it's magic seems a much easier route! 
Regards from
Tom :slight_smile:

#6

Hi Steve,

I only have 4.0.1.2 installed, but my guess is that with the comma in there, or possibly the dollar sign, or maybe both, when you are pasting it, calc thinks it is text and not a number. That causes the left alignment and not being dividable since text can't be divided.

Don

#7

Hi :slight_smile:
I think you might be over-complicating it? Just typign the number in as a
number and letting the formatting do it's magic seems a much easier route!

Well, I don't know what the OP usually does, but maybe he goes to a
website somewhere, highlight a a table or something and then paste it
into calc?

Of course he could then do a Search/Replace to get rid of the
thousands separator and the dollar sign, and form there it is probably
quite a lot easier to do the rest. But it feels like that's at least
one step too much… It's easy to create a macro that does all the
necessary Search/Replace steps in one single step, though.

Johnny Rosenberg

#8

I use LO 3.6.5.2 from the LO website. When I write $2,441.99, Calc recognizes it as a currency. When I copy it and then paste it into a cell, I get the Import Options dialog. At its bottom is the Options and a box that should be checked any time the data contains special numbers such as dates, currencies, etc.
     So I have 2 ways to paste currency: click the box in the Option section, or paste special.
     However, if you have gotten you version of LO from a repository rather than from the LO website, your version may not have the Import Options dialog.

--Dan

#9

Thanks Johnny
I thought easy enough with regexp but noticed that there are instructions on how to do it in excel so thought LO might too.
Steve

#10

Thanks, but it is pasted text that this applies to.
Steve

#11

Thanks for the help. I may have figured it. If I copy and paste from a plain text document it works fine.
If I copy and paste from another source, say a web page I have a problem. If I copy and paste into writer, and then copy again into calc I am fine. I think I have to do this to copy html tables from the web to calc.
Cheers, Steve

#12

Hi :slight_smile:
You can always try to use Paste Special
Shift Ctrl V
and choose "unformatted text".  I'm not sure if that will strip the ' off the front or the " marks from around it but it';s got to be worth a try when pasting stuff from websites. 
Regards from
Tom :slight_smile:

#13

What is your default Language set to?
Before Pasting, Go to Format>Cells and alter the Language to US English.
After Pasting, alter it back to UK English, or whatever currency is the
Default.

Tink.