It’s often frustrating when simple tasks go awry in unexpected ways, like when have spreadsheets where the dates suddenly shift by four years you open or import a comma-separated value file and the decimal amounts appear in a second column. In this article I’ll direct you to an obscure setting within the Windows Control Panel that can make or break your day.
In the United States we accept the period as a decimal marker without question. It’s just the way things are. However, around the world many countries instead use a comma as a decimal marker. This doesn’t really pose a problem until you try to work with comma-separated value files in Excel, where the comma is used to delineate separate fields.
For instance, take a look at the file in Figure 1. Notice that words are enclosed in double quotes, blank fields are signified by adjacent double-quotes, and amounts stand alone. Thus, the comma between 84941 and 39 is going to be interpreted as a field delimiter, and so the file would appear in Excel as shown in Figure 2.
Figure 1: Commas used as decimal markers can pose a problem when working with CSV files.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.