Page 1 of 1

[Solved] Lost in conversion

PostPosted: Sun May 02, 2021 3:54 pm
by jolaer
Hello everyone,

Despit my research, i was unable to find an answer for this (maybe) simple conversion.

I have an external link from which i retrieve a value.
This value is a USD currency with this format (i guess it is a text format: $1,000.00
I am unable to convert it in a number format like this: 1000,00

This conversion would allows me to manipulate this number, unfortunately i am stuck at this point and cannot go forward.

If someone could be nice to help me, it would be great.

Many thanks and a nice day to all,
Jo.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 5:11 pm
by Villeroy
The import dialog has an option "Detect special numbers". This will interprete strings like "May 2 2021", "12:45 am" and currencies. The resulting number can be used in calculations.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 6:29 pm
by jolaer
Villeroy, thank you for you answer.

I've ticked the option while importing and maybe i am wrong but i've tried to play with the imported data but it doesn't work.

After importation, i got this:

A1 (text) --> Current price
B1 (Currency as text) --> $1,200.25

And i filled inside the cell C1 this --> = B1 * 2

So, i was expecting to see in C1 this --> 2,400.50 or $2,400.50 or 2400,50 (the last one the best for me but i will be happy with any format).
Unfortunately, it doesn't work.
I got this in C1 --> #VALUE !

Any idea ?

Many thanks,
Jo.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 7:16 pm
by Villeroy
Did you notice the language option on the same import dialog? This is not a wish list. It gives a hint to the program about how to interprete numeric expressions.
Should be something English in a country with Dollar currency like USA or Australlia.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 7:32 pm
by jolaer
Villeroy,

Thank you very much.
I've changed the linguistic parameters in tools, options, and now it works. I just have to remove a special character just before the dollar sign but this, i saw some post related to this operation so, i will do it.

Many thanks for your help & have a nice sunday!
Jo.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 7:52 pm
by RusselB
Using the Detect Special Numbers option will remove that extra character (looks like an apostrophe) when importing.
That character is what tells Calc that it is text rather than a number.

Re: Lost in conversion

PostPosted: Sun May 02, 2021 8:47 pm
by Villeroy
The global option under Tools>Languages applies to the whole office suite. Whatever import method you are doing (there are many different ways), choose the right import language and "detect special numbers" while importing and everything will be fine without changing global options.