Wednesday, April 27, 2011

Changing decimal separator in Excel

When importing values to Excel, often the decimal separator is not set to the one present in the data being imported. This result in numbers being stored as text and in the case you use these numbers to make calculations you will get #VALUE! as an output. In the example below the decimal separator is set to 'coma' (,) while the imported numbers contain 'period' (.) as a decimal separator and the imported numbers are stored as text, thus the log function results in #VALUE!.

Possible solution is to find/replace period symbols with commas, however there is a way to change decimal separator. In Excel 2003 select Tools/Options/International, uncheck 'use system separator' and type the separator symbol in Decimal separator field. The outputs #VALUE! will immediately change to numerical values as shown below.

1 comment: