If you work in more than one language with Excel, then you know that there are often issues converting numbers from French, Spanish, or other Romance languages to American or British English. The reason for this is that the number $1,000.25 USD is written as $1.000,25 in Romance languages with the decimal and comma switched. Because of this issue, some interesting mistakes have been made in computer systems. One hapless retailer sold $5,000 cameras for $5 each. The consumer protection agency made him stick with that price when he tried to go after customers for the difference. One law firm billed clients for 40 hours in one day when the attorney had only worked 4. Needless to say, the client thought he had been cheated. Luckily, there is an easy fix for this confusion.
Microsoft Excel does not know what to do with numbers that are in a format that is different from the language of the Excel version you are using. Therefore, it treats such strings of text as ordinary text, not numbers. That is not good as you cannot do mathematics with text. You need to convert the text to number format.
1.Here is an example. Below is a price list. As you can see, this number format uses commas for decimals and decimals for the 1000’s separators. It is from Europe or Latin America.
2.When you open that text file with Excel, it looks like the screen shown below. You can tell that Excel thinks this is text and not a number because the text is left-justified. Numbers are right-justified.
In the next column, we see the results of converting this European/Latin American format to English. The numbers are right justified, and the cents portion of the dollar amount is shown.
To the right is the formula used to translate from one format to another: ‘=NUMBERVALUE(A1,”,”,”.”). The arguments are the text value, decimal separator, and 1000’s separator in that order.
3.It is easier to see what the arguments are for the NUMBERVALUE function if you use the function wizard like this:
