Clever Geek Handbook

Insert decimal numbers in excel / comma and dot decimal separator

I want to get data in excel using DDE. I get a new value every second from DDE and use a macro to store each value on the next line. So that's so good.

The problem is the decimal separator.

I am getting from DDE a value like -5.18834 By using a macro or pasting it into excel the value shows -518.557 but the actual value is -518557. The decimal point is misinterpreted by excel. When I paste the same value into notepad, I get the correct value, i.e. -5.18557 I tried to define the value at 10000 in excel, but some values ​​are 5 numbers instead of 6, so again this is wrong.

I've tried formatting cells, changing the decimal separator in the excel options, changing the regional settings on my computer, but none of them work. I also tried adding TextFileDecimalSeparator = "." in a macro (found on this site), but that doesn't work either. Processing the data after the macro has finished is not an option.

Thanks for any help.

-2


source to share


3 answers


Your listing of what you've already tried seems to rule it out, but I would be very confused if the following didn't work:



Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False

' here do your paste

Application.UseSystemSeparators = True
+1


source


I had a similar problem - the decimal numbers that I passed with the macro to the cells were accepted by excel as text ... So I tried exactly the same procedures as you did ... Didn't help ... I tried even the above solution ... that didn't help either. This is a great solution. Unfortunately this was not helpful in my case, the macro just ignored it and used a different seperator instead. I tried a lot of things and I felt desperate and then I came up with this awkward solution:

  Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


The macro ignored the setting of the decimal separator, so I decided to replace this separator with the right one. It works! As I said, this is a clumsy solution, but so far it works ...

0


source


There is a faster way without writing a macro. You can change separators through Excel options.

https://social.technet.microsoft.com/Forums/office/en-US/10eb503a-2076-4e80-8488-82495e051b30/how-to-change-the-thousands-separator-temporarily-in-excel-2010? forum = excel

0


source






More articles:


All Articles