Why Decimal Amounts Sometimes Appear in a Second Column in Excel

Spreadsheets and graphs on a desk
xfgiro/istock

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

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.

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.