Correcting numbers in Excel with incorrect commas, points or spaces

With the various regional settings available in the world, a common issue in MS Excel is numbers that Excel does not recognise as numbers, and as a result they don’t sum properly and can’t be used as numbers. This is one (new) way of correcting numbers in Excel with incorrect commas, points or spaces.

Commas for Thousands and Points for Decimals

As shown below, my current regional settings are set such that numbers use spaces to separate the thousands, and a decimal point for decimals. So on my computer the number 1438722.123 would appear as 1 438 722.123 .

Correcting numbers in Excel with incorrect commas points or spaces

The problem with this setup is that if my computer gets a file with a number like 594,399.23 it assumes that this is NOT a number, as the comma does not belong in a number. If my computer was set up to accept the comma as a thousand separator, then numbers with spaces would cause it problems. So above you will see the we get a #VALUE error when we try and use the numbers.

As a result you need to be able to easily convert  numbers from different regions into usable Excel numbers.

There are a number of ways to do this (see our Data Cleanup Course for more), but a fairly new formula is the NUMBERVALUE formula.

VALUE vs NUMBERVALUE

You must not confuse NUMBERVALUE with VALUE. VALUE tries to guess the region and often still cannot get it right. The reason NUMBERVALUE works better is because it asks you what the decimal separator and group separators are in the cells that aren’t recognised as numbers.

As shown below, in the function you are asked to specify the decimal separator and group separator. It is very important to remember that this is NOT asking how you would like to see it, but rather what have you been given. As shown below in (1) we told Excel that the cell in A9 is using a . for a decimal and in (2) we told it that the group separator is a , . Excel will correct for this and show the “number” as a number.

Correcting numbers in Excel with incorrect commas points or spaces

Below how to handle a number that uses spaces as a separator but a comma for a decimal (where my computer is expecting a point). In (1) below we tell Excel that the number uses , as a decimal and (2) a space as the group separator. Excel will correct for these regional issues and you will be able to use your numbers.

Correcting numbers in Excel with incorrect commas points or spaces

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.