CODEWITHSUNDEEP
excelperiod
user136128
user136128

Reputation: 239

Excel: Change "," to "."

Ever since i added a multiple language option for my computer, Excel decided to turn all my decimals into commas. I need to turn them back into decimals again. How do i do this with least amount of work? If you know an EASY way to do this, emphasis on easy, please tell. When it is converted, i need a number, not text or anything else. I'm using Microsoft Office Professional Plus 2010.

I tried the approach where you make this formula in Excel

=SUBSTITUTE(A4;",";".")+0

Which should, i'm assuming, get cell A4, change comma into period and then by adding 0 convert to number. My original number is 17.6, now i'm getting 41807.

Upvotes: 9

Views: 68258

Answers (2)

Asif DanS
Asif DanS

Reputation: 79

My best choice to use the below function which can help to convert the text to numbers also at the same time. Its very useful in cases where some systme reports are shared with different number formats

=NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(E3,".",""),",","."))

Upvotes: 5

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

There're two options for you.

1) change regional settings on your PC:

enter image description here

2) use Application.DecimalSeparator in VBA code (e.g. in Workbook_Open event):

Private Sub Workbook_Open()
    Application.DecimalSeparator = "."
End Sub

enter image description here

Upvotes: 5

Related Questions