Excel Data - reading decimal as thousands seperator

[cbcmlt]

Hi,
I just loaded data from a compression test machine as a .TSX file. I'm having problems plotting my data because numbers use comma as a decimal seperator - so all values larger than 1 are automatically read as thousands.

Does anyone have an idea how to ammend this? attached excel file as example

thanks!

[MrShorty]

Not sure exactly how you want to approach this. A few thoughts:

1) What exactly is a .TSX file? A Typescript file? How is the data coming into Excel? Is it code within the .TSX file that writes the data to Excel, or is Excel importing the data as text via something like the text import wizard? Can you change anything about how the data is imported/written into Excel? You haven't said anything about this importation step, but my first inclination would be change something in the importation step so that the data comes into Excel with the correct decimal separator.

2) Most installations of Excel default to "use your OS regional settings for decimal and thousand separator". Of course, you can change this in Excel options, but it is still an Excel global setting. Could you change your decimal and thousand separator setting before importing this data to Excel, so Excel will correctly read it? If you are importing the data via the Text Import Wizard, you can specify the decimal and thousands separators using the "advanced" options button at step 3 of the text import wizard.

3) If you cannot somehow change the decimal separator, can you change something about the importation step so that the data forced to be text (preformat cells as text, for example)? If so, then you can easily perform a "text to columns" command after import where you can specify the decimal separator setting (step 3 of the text import wizard) as you convert the text to numbers.

4) As a last resort, a formula based solution (post import, obviously) could be:
4a) For the numbers between -1 and 1 which are imported as text, convert them to numbers with something like VALUE(SUBSTITUTE(text,",","."))
4b) for the numbers less than -1 and greater than 1 (which are already seen as numbers), you know that they are a factor of 1E3 off, so divide them by 1000 -- number/1000
4c) Nest those two options inside of an IF() function that will decide which action to execute. Maybe =IF(ISTEXT(A3),VALUE(SUBSTITUTE(A3,",",".")),A3/1000)

Not sure how you would like to proceed, but that should give you some ideas of where to look.