Excel: Change "," to "."

Question

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.

you should change [PC regional settings](http://blogs.technet.com/b/askperf/archive/2012/08/16/how-to-change-regional-settings-for-all-users-on-a-computer.aspx) — Dmitry Pavliv, May 24 '14 at 20:42
or change decimal separator using VBA: http://msdn.microsoft.com/en-us/library/office/ff195207(v=office.15).aspx — Dmitry Pavliv, May 24 '14 at 20:47

score 5 · Answer 1 · answered Jun 25 '18 at 15:00

5

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,".",""),",","."))

answered Jun 25 '18 at 15:00

Asif DanS

79
1
2

Useful formula, but has problems with negative numbers – BOB Apr 02 '20 at 12:03

score 4 · Accepted Answer · answered May 24 '14 at 21:05

4

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

answered May 24 '14 at 21:05

Dmitry Pavliv

35,333
13
79
80

What about OS X? – Ramy Al Zuhouri Jun 04 '17 at 20:36

Excel: Change "," to "."

2 Answers2

Linked