Convert regional number formatting in Excel with NUMBERFORMAT

Different countries have different ways of formatting numbers, with some using a comma or a full stop to separate thousands, and some using them as decimals.

As Excel is used in many countries and is formatted to that country’s preference, this can cause issues when a number is written in one format in one country's Excel and then shared with someone in another country which uses a different number format.

This can lead to numbers being recognised as text instead of a numeric entry, causing calculations to break and causing errors when trying to use those numbers in other calculations.

Download File

If you would like to follow along please download the file below:

Convert regional number formatting in Excel with NUMBERFORMAT.xlsx

Download XLSX • 12KB

What is NUMBERVALUE

The NUMBERVALUE function converts a text based number into a numeric value based on the decimal and group separators you specify.

The syntax is:

=NUMBERVALUE(text, [decimal_separator], [group_separator])

• text - The number stored as text that you want to convert.

• decimal_separator (Optional) - The character you want to use as the decimal separator. This helps when numbers from different countries use different symbols for the decimal point.

• group_separator (Optional) - The character you want to use as the thousands separator. This is helpful when the thousands separator varies, such as commas in some regions and full stops in others.

Regional differences between number formats

In countries like the UK or USA numbers use a comma to separate thousands and a full stop for decimals:

1,500.50

In Germany this is reversed, with a full stop for thousands and a comma for decimals:

1.500,50

If someone in Germany entered the value with full stop for thousands and commas for decimals and sent it to someone in the UK, when the person in the UK opened the file it would be recognised as text and not be recognised as a number so could not be used in calculations.

Impact of regional number differences in Excel

You can only have one region set in Excel, so if you have UK settings it would not be possible to add a UK formatted number to a German formatted number.

Excel will treat any number not matching your regional format as text, and using it in a calculation will return an error.

To fix this, we need to convert the German formatted number into a numeric value format recognised in the UK.

Using NUMBERVALUE to change to a number

To convert the German number formatting to the UK number formatting you can use the NUMBERVALUE function.

The function converts the value in the following way:

It is important to use quotation marks around any characters used to separate parts of a number.

The converted NUMBERVALUE can now be used in calculations as Excel is now treating the output as a number rather than text.

NUMBERVALUE can also be used directly in formulas.

Applying NUMBERVALUE across a range

If you have a column of foreign number formats you can use NUMBERVALUE across the whole range.

To do this just select the range of cells that your data is in in the text parameter of the function.

This will return an output across a range of cells.

The range the output extends over is called the spill area and is shown surrounded by a blue line.

Only the first value in the spilled range will allow you to make changes to the function. You will be able to tell this because the text in the formula bar will be black.

When you select any of the other values in the spill area the text will be grey and you will not be able to make any changes.

Conclusion

If you receive an Excel file with numbers formatted in a way that is not recognised in your region settings the number will be treated as text and cannot be used in calculations.

Using the NUMBERVALUE function you will be able to convert the format to your country's style and use it in formulas.