[Solved] Lost in conversion

[Solved] Lost in conversion

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.
Last edited by Hagar Delest on Sun May 02, 2021 9:05 pm, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.7
jolaer

Posts: 3
Joined: Sun May 02, 2021 3:44 pm

Re: Lost in conversion

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lost in conversion

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.
OpenOffice 4.1.7
jolaer

Posts: 3
Joined: Sun May 02, 2021 3:44 pm

Re: Lost in conversion

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lost in conversion

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.
OpenOffice 4.1.7
jolaer

Posts: 3
Joined: Sun May 02, 2021 3:44 pm

Re: Lost in conversion

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6397
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Lost in conversion

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany