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!

 

[GabrielAIK]

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.

May I ask what you mean with "100,00-999,00" contains a minus? Do you mean the actual mathematical sign? - if so, I apologize, because I meant the "-" sign as a range - or is it something in Excel?

 

[GabrielAIK]

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

You're right. It works fine.

What I realized in my other Excel sheet is that if I copy a number, e.g. "8,205" from a website, and paste the values in Excel, then Excel removes the decimal all together and instead gives me a value of "8205" which is quite strange.


I guess, in this formula sheet I can do something similar to what I wrote in my original post, i.e. something like this: =LEFT(W48,1)+RIGHT(W48,3)/1000

But I guess the problem would remain that this formula would only work for values between 0 and 10.

 

[Peter_SSs]

Perhaps I have misunderstood, but don't you just need this?

=SUBSTITUTE(SUBSTITUTE(W48,",",".")," ","")+0

 

[GabrielAIK]

Perhaps I have misunderstood, but don't you just need this?

=SUBSTITUTE(SUBSTITUTE(W48,",",".")," ","")+0

That one worked great as well, thanks!


My final problem now is when I have a number with 3 decimal points, which my Excel interprets by removing the decimal.

So when I copy a number with 2 decimals separated by a comma, e.g. "8,02", then the formulas above works perfectly as the formula substitutes the comma to a decimal, and all is well.

However, some share prices online have three decimal points, e.g. "8,205", and for some reason, my Excel interprets the number by removing the comma all together. So instead of pasting a value of "8,205" which the formula above would solve, it instead pastes the number "8205". This means that I manually have to add a decimal points for all values.

My only solution thus far, which I pasted above, merely works for numbers between 0-9.999, where I use LEFT() and RIGHT (). Formulas.

Is there a better way to solve this? I mean if I use the formula I wrote above, and the share price would be "10,205", then it would not work :/

 

[Peter_SSs]

That one worked great as well, thanks!

Great. Thanks for letting us know.

However, some share prices online have three decimal points, e.g. "8,205"

If you click on the original cell that contains that value, what (exactly) do you see in the formula bar?

 

[GabrielAIK]

Great. Thanks for letting us know.


If you click on the original cell that contains that value, what (exactly) do you see in the formula bar?

Sorry for taking the pictures with my cellphone instead of taking a screenshot, but file sharing and image storing websites are blocked at work.


This is how it looks when I paste the values from the Internet link: https://i.imgur.com/l4WT8PNr.jpg

And this is how it looks in the formula bar: https://i.imgur.com/W60Tanp_d.jpg?maxwidth=640&shape=thumb&fidelity=medium


So in the formula bar it only says "8205", thus removing all commas.

So to clarify: when I paste the value, it shows as "8,205", but when I click on it, the formula bar says "8205"


If I'd only copy the first 2 decimal points from the Internet site, i.e. if I'd only copy "8,20", then the formula bar would show "8,20". But when I copy the three decimals, it just removes the decimal

 

[Peter_SSs]

So to clarify: when I paste the value, it shows as "8,205", but when I click on it, the formula bar says "8205"

That is because in your system the comma is the thousands separator and so (quite reasonably) sees what you are pasting as the number 8205.

Try formatting your column as Text before pasting the data from the internet in it and see how that goes.

 

[GabrielAIK]

You guys are wizards!

That works great.

So what I did was to format the cells where I'm going to paste the values that have 3 decimal points as "Text" instead of General.

When I copy the values from the Internet site, i press CTRL alt E S V and paste as "Text". Then it pastes as "8,205" and the formula "=SUBSTITUTE(SUBSTITUTE(W48,",",".")," ","")+0" works as a charm here as well!

 

[Peter_SSs]

Glad you got a successful outcome in the end.