[Solved] Lost in conversion

Discuss the spreadsheet application

[Solved] Lost in conversion

Postby jolaer » Sun May 02, 2021 3:54 pm

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

Postby Villeroy » Sun May 02, 2021 5:11 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lost in conversion

Postby jolaer » Sun May 02, 2021 6:29 pm

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

Postby Villeroy » Sun May 02, 2021 7:16 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lost in conversion

Postby jolaer » Sun May 02, 2021 7:32 pm

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

Postby RusselB » Sun May 02, 2021 7:52 pm

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.
User avatar
RusselB
Moderator
 
Posts: 6397
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Lost in conversion

Postby Villeroy » Sun May 02, 2021 8:47 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests