Excel: Change "," to "."

excel comma period
user136128 picture user136128 · May 24, 2014 · Viewed 41.5k times · Source

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.

Answer

Dmitry Pavliv picture Dmitry Pavliv · May 24, 2014

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

Related questions

Why is VBA changing decimal to comma automatically?

I have an Excel macro in VBA. Yesterday everything worked fine, this morning VBA is changing a decimal point to a comma. That is from 5.1 to 5,1. I am using a German system and have set in Excel's advanced options that …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More