Forum Discussion

ottothek's avatar
ottothek
Copper Contributor
Mar 01, 2022

Cannot edit thousands separator

Hi,

 

Can anyone tell me why I get an error message everytime I try to add a comma to my thousands separator. I have tried to change region and it will not change? I keep getting the error noise.

 

 

excel

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Mar 05, 2022

    ottothek 

    Many roads lead to Rome, it was said in ancient times.

    Nowadays one would say that there are many ways that can lead to a solution in Excel.

     

    with formula:

    =SUBSTITUTE(A1,".",",")*1

    …and formula  =--SUBSTITUTE(SUBSTITUTE(A1,",",""),".",",")

    when formatted afterwards with custom cell format as #.##0,00.

     

    or... thousands separator

    In Excel, open "File" in the top left, then select "Options" on the left.

    In the "Advanced" category, uncheck "Use separator from operating system".

    Then enter the desired character (i.e. a point) for "Decimal separator" and "Thousand separator" and click on "OK".

     

    or…

    custom cell format: #,##0

     

    or...with VBA

    a examble

    Sub seperator()
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
Range("B1").Formula = CDbl(Range("A1"))
Application.UseSystemSeparators = True
End Sub

     

    Hope I was able to help you with this information.

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources