0

How can I convert numbers in the format such as 0.8805*0.1 to actual number?

In my case above, the result should be 0.08805. I would like to apply this to a huge number of columns and rows.

sanjihan
  • 5,592
  • 11
  • 54
  • 119
  • If I understand you correctly, you have cells with the strings like `0.8805*0.1` and want them calculated? If so, could you give a small sample? And: what have you tried so far? – Tom K. Aug 09 '16 at 14:02
  • I tried adding = infront of my cell, but it causes an error. Possibly due to excel not recognizing decimal numbers formatted with . sign. I am on mac. – sanjihan Aug 09 '16 at 14:08
  • 1
    If it's as @Tom suggest then this will work: http://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula. Take note of the comment left by Tim Williams though. – Darren Bartrup-Cook Aug 09 '16 at 14:08

1 Answers1

0

I'm on windows. so i purposely put decimal point as ",".

enter image description here

First thing to do is replace the commas into points (or points to commas to suit your need in mac). so in B1, i convert it with =SUBSTITUTE(A1,",",".").

For formula in C1, first select the cell you want to place the calculated output (its C1 in my example), then create a name range as follow with the formula =EVALUATE(Sheet1!B1). After that place the formula =EvaluateIt in C1.

enter image description here

after that just fill down the formula and you can convert text formula into its calculated value.

enter image description here

Rosetta
  • 2,665
  • 1
  • 13
  • 29