CODEWITHSUNDEEP
excel
sosruko
sosruko

Reputation: 1079

How to convert numbers from European format to British in excel (separator commas to dots)?

I have excel files containing data in European format. Thousand separator is a dot, but I want to use a comma. I can easily convert it using =SUBSTITUTE function. However, when I do that I lose zeros at the end of the number. For instance 11.500 (converts to) 11,5 (eleven thousand five hundred becomes eleven and a half)

After the conversion, I tried to change the custom format, but I had no luck.

Any ideas?

Upvotes: 2

Views: 6601

Answers (2)

Doll Sixeeteinn
Doll Sixeeteinn

Reputation: 1

I use this, just for your reference

=SUBSTITUTE(SUBSTITUTE(A1,".",","),",",".",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))+1)

Upvotes: 0

AlbertLau14100
AlbertLau14100

Reputation: 116

A). Using The Excel Formula

The problem you faced is due to the auto-conversion done by Excel to treat '.' as the DecimalSeparator. The below formula uses '&""' to force Excel to treat the value in the cell as the Text value instead in the Substitute function. The Value function will then force the result of the Substitute (in Text) to a number. You can then apply your desired NumberFormat accordingly.

=VALUE(SUBSTITUTE(<Cell Address>&"",".",""))

e.g.

=VALUE(SUBSTITUTE(A1&"",".",""))

B) Using The VBA code

You can use the below to force the system to revert to 'dot' as the decimal separator and ',' as the thousand separator. 

Sub OverrideSystemSeparators()
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
End Sub

The above code assumes that the excel file is opened using the Europe system with default Decimalseparator (',') and Thousandseparator ('.').

However, if you open it using UK/US system and the data is still in Europe format, do validate if the data is in Number format or Text format.

If it is in Text format, do use the below code instead -- you will need to replace the last 2 statements based on the data range you would like to convert. In brief, it replaces '.' with nothing and then, applies the NumberFormat to add ',' as the thousand separator.

Sub OverrideSystemSeparators()
    Application.UseSystemSeparators = True
    ActiveCell.Value = Replace(ActiveCell.Value, ".", "")
    ActiveCell.NumberFormat = "#,##0.00"
End Sub

Upvotes: 1

Related Questions