March 25, 2022

Working with European Decimal Numbers in Excel

How to work with European decimal numbers in text boxes on User Form in Excel. Watch the video below:

Working with European decimal numbers in Excel

Europeans use the comma instead of the dot in decimal numbers. One of the main reasons is that their numerical keypad has only a comma. Therefore, finance professionals who tend to enter decimal values via the numerical keyboard use the comma.

Danish keyboard
Danish keyboard – the numeric keypad has a comma

If we have huge European decimal numbers like statistical data, we can easily convert them to standard dot decimal numbers using the ‘NUMBERVALUE’ formula as shown below:

Using NUMBERFORMULA
Using NUMBERFORMULA to convert European decimal numbers using comma to dot decimal numbers

You can read more about the NUMBERVALUE function here.

Another way to handle European decimal numbers in an Excel worksheet is to disable the ‘Use system separators’ by clicking on File –> Options –> Advanced as shown in the images below:

Use System Separators enabled
Use System Separators enabled
Use System Separators disabled
Use System Separators disabled

For example, if we create a workbook in the USA and share it with someone in Denmark then the user in Denmark will see the numbers according to his Regional settings in Windows.
If the display of the decimal numbers doesn’t happen automatically then it only means that we have configured Excel to ignore the ‘Use system separators’ as shown below.

Calculations with decimal numbers using comma by disabling 'Use System Separators'
Calculations with decimal numbers using comma by disabling ‘Use System Separators’

Now handling European decimal numbers in text boxes on a user-form and transferring them to an Excel worksheet becomes very interesting, especially if we wish to perform calculations with the European decimal numbers. We’ll use the concepts above in our macro that automates the complete process of transferring European decimal numbers from UserForm text boxes into an Excel worksheet:

Private Sub Workbook_Activate()
Application.UseSystemSeparators = False
End Sub

Private Sub Workbook_Deactivate()
Application.UseSystemSeparators = True
End Sub

Sub ShowUserForm()
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
Dim CommaPos As Long, NumCharsAfterComma As Long

CommaPos = InStr(TextBox1.Text, “,”)

‘MsgBox CommaPos

NumCharsAfterComma = Len(Mid(TextBox1, CommaPos + 1))

If NumCharsAfterComma > 2 Then
MsgBox “You’ve entered too many numbers after the commain TextBox1. Only two numbers allowed.”
Exit Sub
End If

Range(“B1”) = TextBox1

Range(“B1”) = Range(“B1”) * 0.01

CommaPos = InStr(TextBox1.Text, “,”)

NumCharsAfterComma = Len(Mid(TextBox2, CommaPos + 1))

If NumCharsAfterComma > 2 Then
MsgBox “You’ve entered too many numbers after the comma in TextBox2. Only two numbers allowed.”
Exit Sub
End If
Range(“B2”) = TextBox2
Range(“B2”) = Range(“B2”) * 0.01

Range(“B3”) = Range(“B1”) * Range(“B2”)
Range(“B1:B3”).NumberFormat = “0.00”
TextBox3 = Range(“B3”).Text

End Sub