Why Decimal Amounts Sometimes Appear in a Second Column in Excel

Feb 18th 2016
Share this content
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.

Decimal Amounts figure 1

Figure 1: Commas used as decimal markers can pose a problem when working with CSV files.

Decimal Amounts Figure 2

Figure 2: The commas caused the numbers to be placed in two separate columns.

Files that contain double-quotes around text are typically created outside of Microsoft Excel. If you open a file such as the one shown in Figure 1 in Excel and then save it back as a CSV file, then the double-quotes will vanish, although the commas between the fields will remain, as shown in Figure 3.

Decimal Amounts Figure 3

Figure 3: The double-quotes will disappear if you reopen a CSV that has been opened and saved in Excel.

If this is happening to you inadvertently, then most likely one of your Regional Settings in Windows is set incorrectly. Regional Settings are designed to enable you to adapt Windows to the agreed-upon conventions for date and currency formats, as well as other options. To do so, you must access the Regional Settings area of Windows:

  • Windows 7: Click Start, and then choose Control Panel, and then Region and Language.
  • Windows 8 and later: Right-click the Start button and choose Control Panel. Alternatively use the Windows Search feature to search for Region and Language Settings

At this point the Region and Language dialog box shown in Figure 4 should appear on-screen:

  1. Click the Additional Settings button.
  2. Change the Decimal Symbol as needed (for instance, to a period from a comma, or vice versa).
  3. Click OK to close the Customize Format dialog box.
  4. Click OK to close the Region and Language dialog box.

Decimal Amounts Figure 4

Figure 4: Use the Region and Language dialog box to change the decimal symbol.

If you have a situation where you aren’t at liberty to change your regional settings, but need to get a comma-separated value file into Excel in a usable fashion, you’ll need to use formulas to straighten things out, as shown in Figure 5.

Decimal Amounts Figure 5

Figure 5: If you aren’t able to change the decimal symbol, you can use formulas to fix the issue.

In both cases the formulas test if column B is blank. In the case of the Debit amounts in column F, if cell B3 is blank then we know the amount must be a credit balance, so the formula displays two double-quotes to make the cell appear as blank. Otherwise the ampersand joins the values in cells B3 and C3 together, placing a comma in the middle.

In column G we again test to determine if cell B3 is blank. If it is, we combine cells C3 and D3 together with a comma in the middle, otherwise we make the cell appear to be blank.

Was this article of use to you? How could it be better? Comment below or post a blog response.

Replies (0)

Please login or register to join the discussion.

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