Nested IF-formulas + commas to decimal points

[GabrielAIK]

Hi,

I have a quite cumbersome (?) question I would love to receive tips regarding.


So I have a cell (W48) where I will have a share price. However, this share price will have commas instead of decimal points. Now, I want to turn this number to a "correct" one in cell W39.

So let's say cell W48 is "228,0", I want the cell W39 to display the number "228.0".

The difficulty is that the share price can go from basically 0-1,000.

What I have done thus far (which is incorrect) in cell W39 is to type this formula:

=IF(W48>=100,LEFT(W48,3)+(RIGHT(W48,2)/100),IF(LEFT(W48,2)>=10,LEFT(W48,2)+(RIGHT(W48,2)/100),IF(LEFT(W48,1)>=1,LEFT(W48,1)+(RIGHT(W48,2)/100),"ERROR")))


So what I am trying to do is:

If cell W48 is 100.00-999.00, I am going to use a "LEFT"-formula to take the three left values, and then the "RIGHT"-formula to take the decimal points.... Etc.

However, this does not work for all values.

If I write a number between 100,00-999,00 in cell W48, it works, but if I write e.g. "22,00" in that cell, then I get a #VALUE!-error in cell W-39.



Sorry if I don't make any sense, I can try to clarify if you don't understand my problem.

Appreciate all help I can get!

 

[Special-K99]

So W48 can contain either a number or what looks like a range, in both cases there is no decimal point just a comma
So W48 can contain figures like
22,00
100,00-999,00

So what do you want the output to be if W48 contains 100,00-999,00 ?

 

[Special-K99]

Try this

=LEFT(SUBSTITUTE(W48,",",".")&"-",FIND("-",SUBSTITUTE(W48,",",".")&"-")-1)+0

 

[GabrielAIK]

Worked like a charm!!! Thank you very much. Baffled how you even understood what my question was.

Is it possible to at a substitution for spaces also?

Your formula works excellent for numbers between 0-999,999


However if I have a share price of e.g. "9 541,00", is it possible to add a substitution that removes the spacing as well?

 

[GabrielAIK]

Is it possible to add* a substitution

 

[Special-K99]

Here you go

=LEFT(SUBSTITUTE(SUBSTITUTE(W48," ",""),",",".")&"-",FIND("-",SUBSTITUTE(SUBSTITUTE(W48," ",""),",",".")&"-")-1)+0

I just added a SUBSTITUTE null "" for spaces around each W48

 

[GabrielAIK]

Here you go

=LEFT(SUBSTITUTE(SUBSTITUTE(W48," ",""),",",".")&"-",FIND("-",SUBSTITUTE(SUBSTITUTE(W48," ",""),",",".")&"-")-1)+0

I just added a SUBSTITUTE null "" for spaces around each W48

That is a clever solution! Thank you very much for the help. Works perfectly now.

May I ask you what the "-1" means in "Num_chars" and the final "+0"?

 

[GabrielAIK]

My final question is:

This works excellent if there are 2 decimal points, e.g. "100,00".

However, if I have 3 decimal points, e.g. "8,205", this wouldn't work, but I guess you can adjust the num_chart (?) for it to solve this issue where some share prices have 3 decimal points instead of 2?

 

[Special-K99]

Our two examples are 22,00 and 100,00-999,00
One contains a minus the other doesnt, so Im deliberately adding a minus to each value so theres definitely a minus in there, so we get 22,00- and 100,00-999.00- (doesnt matter there are two minuses), the result in both cases is a string as there is a minus at the end.

So the overall formula says take the left of the string up to the minus FIND("-",...) but we dont want the position where the minus is we want the position before so its FIND("-",...)-1

So LEFT(string,FIND the minus but go one position back)

The +0 just changes the result into a number
You may see -- at the front sometimes in formulas, this does exactly the same thing, turns a result into a number.

 

[Special-K99]

My final question is:

This works excellent if there are 2 decimal points, e.g. "100,00".

However, if I have 3 decimal points, e.g. "8,205", this wouldn't work, but I guess you can adjust the num_chart (?) for it to solve this issue where some share prices have 3 decimal points instead of 2?

Works ok for 8,205
I dont think it matters how many decimal places.