french number conversion

[john1968]

Hello,
I am wondering if asyone knows how to convert french (Canadian) numbers to English on a spreadsheet. I copied data from a website, pasted it on a new excel sheet, but I cant work with the numbers because they are unrecognized because the decimals are replaced by commas: IE $47.24 is written as 47,24. Any ideas. I thought of using LEFT RIGHT functions but that seems unwieldly.
Thanks
John

 

[MrKowz]

Format column B as currency, and use:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">47,24</td><td style="text-align: right;;">$47.24</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">65,30</td><td style="text-align: right;;">$65.30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2,3</td><td style="text-align: right;;">$2.30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">6</td><td style="text-align: right;;">$6.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0,67</td><td style="text-align: right;;">$0.67</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=VALUE(<font color="Blue">SUBSTITUTE(<font color="Red">A1,",","."</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[VoG]

Try Edit > Find > Replace , with .

 

[john1968]

Thank You!! I will give both a try!

 

[john1968]

The substitute function worked. Thank you very much! (I didnt try the edit >Find>Replace) Thanks
John

 

[Shaan Bhasin]

Hi!

This will work only with the number in hundred's but if number is 7,820,94 then it will not work.
Any solution for such kind of number ?

Thanks
Shaan

 

[Shaan Bhasin]

Found it myself:
=IF(LEFT(RIGHT(J2,3),1)=",",SUBSTITUTE(J2,",",".",2),J2)