HOW TO CONVERT NUMBER ENTERED AS TEXT TO NUMBER FOR COMPUTATION

[REENA GUPTA]

Hi
I am applying a formula in column D but it is not giving the desired result as mentioned in column E because values in column C are entered as text. I also tried TEXT TO COLUMN option from DATA menu but it also didn't help in converting the values to numbers for computation.

Please advise how can I convert the values to numbers
Regards

 

[bosco_yip]

D5, copied down :

=0.06/100*SUBSTITUTE(C5,CHAR(160),"")

Regards
Bosco

 

[REENA GUPTA]

D5, copied down :

=0.06/100*SUBSTITUTE(C5,CHAR(160),"")

Regards
Bosco

Thanks Bosco it is working. I shall be grateful if u could tell what is Char(160) & how would u detect it's presence
Regards

 

[pecoflyer]

Perhaps search on the Net for the Char(160)?

 

[Peter Bartholomew]

The other place to find ASCII character 160 is to pick 'symbol' on the insert ribbon tab (menu if you are using Office 2003). In the attached, I have used a table for the data and a named formula to hide the 'plumbing' that distracts from the business logic.

 

[REENA GUPTA]

Thanks Peter. this is another option we have