Deal with values that come with $, EUR etc?

[dellor]

For example, you want to track a price of an asset in real time, let’s say gold, so you instert the HTML_All thing from a website.

You get the cell you wanted which gives you a $1.800 (price of gold) and updates every X seconds (I need the . because in my country it works like that, commas are for decimals)

Good, but now I need this to be in EUR. What I would do is get this:

https://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=EUR

Now I import this and I get the 0.848331 EUR cell.

Now what I want to do is to convert $1.800 cell equivant to euros, so I would need to multiply $1.800 cell * 0.848331 EUR cell, problem is I get a #VALUE! error because it has the $ and EUR signs at the end.

How can I somehow do this? I have tried to find websites that just give you the plain number without the sign next to it but I cant find them so hopefully there is a way to do this.

Thanks

[Wanderer]

If your cell contains text you have to convert it:
In your example $1.800 may be in A1, so you can in a first step remove the $ for example as

=RIGHT(A1; LEN(A1)-1)

and have 1.800 as text in B1.

Then there is NUMBERVALUE( B1; "," ; ".") to get a real number wich you put in C1.
This field you may format a necessary, and you can use it for calculations.

J.

PS: Not tested, as my locale is not set to english - but I hope you get the idea.

[EarnestAl]

If dellor sees the price of gold on his spreadsheet as $1.800 then it is already a number, otherwise he would see $1,800 which is what appears on websites.

The problem then is just the EUR. If it links in from the website as unformatted text then formatting the cell as currency EUR might be sufficient for it to be recognised as a number formatted as currency. Otherwise, using @Wanderer method if 0.848331 EUR is in cell C1 then in cell D1 you could have =VALUE(LEFT(C1,LEN(C1)-4)) and format D1 as EUR to 5 decimal places #,##0.00000 [$EUR];[RED]-#,##0.00000 [$EUR]

[Wanderer]

I was not sure wich part of the website he has linked and if he may even see different values as me…

Another easy approach would be to use SUBSTITUTE() to replace the currency.

But the basic line is:
Use Text-functions to clean your input, then use VALUE to get something to calculate, if necessary.