CODEWITHSUNDEEP
excelexcel-formula
AndreaNobili
AndreaNobili

Reputation: 42957

How can I change the decimal separator from "." to "," only for a specific cell using Excel?

I am absolutly new in Excel and I have the following problem multiplying 2 values.

I will try to explain you my situation.

Into a sheet named currenciesInfo I have that each row is a currency and the P coulmn contains the value in € of this currency.

I am retrieving the content of this currenciesInfo sheet connecting Excel to an API.

The retrieved value is something like 728.454911165 and it is formatted as a number.

Then into another sheet named ETH I retrieve the € value of a specific currency by this formula:

=INDIRECT("CurrenciesInfo!"&ADDRESS(MATCH("ETH";CurrenciesInfo!C:C;0);16))

and putting this value into the K6 cell of this ETH sheet

Ok, now in this ETH sheet I have another field named K2 field containing a number like 0,0783272400

The problem is that when I try to multiply the K6 and the K2 cell by this simple formula:

=K3*K6

I obtain a non sense value like: 57.057.862.655,9996000000

I think that the problem is related to the fact that into the CurrenciesInfo sheet I am retrieving this value 728.454911165 that use the . symbol as decimal separator while into the other sheet the decimal separator is ,.

Infact if into the CurrenciesInfo sheet I manually change the . symbol with the , symbol. The calculation is done correctly and this formula:

=K3*K6

get me the correct result that is 57,0578626560

So, how can I handle this situation? The problem is that the values into the CurrenciesInfo sheet are automatically retrieved using an API that provide me the data only in this format.

Exist a way to do something like this?

1) Into the CurrenciesInfo sheet the value is retrieved by the API in this format 728.454911165 having the . as decimal separator.

2) When into the ETH sheet I retrieve this value using this formula:

=INDIRECT("CurrenciesInfo!"&ADDRESS(MATCH("ETH";CurrenciesInfo!C:C;0);16))

I change in some way the . symbol with the , symbol (so I have the expected value with the , in the K6 cell and the multiplication should work fine)

Can I do something like this?

Upvotes: 2

Views: 1493

Answers (1)

pnuts
pnuts

Reputation: 59475

@shrivallabha.redij has explained that the separator is either at the operating system or application level - what you ask is not possible. However the result 57,0578626560 can be achieved with your settings if you include division by 10^9 in your currency calculation:

 =K3*K6/10^9

You should also format the result as General to avoid lots of full stops in the answer (and heed @shrivallabha.redij advice regarding the volatile INDIRECT if your sheet shows any signs of being slow to update).

And, IMO, it would have been better to ask this and your previous question on Super User rather than here.

Upvotes: 1

Related Questions