(un)Formatting nightmare (eporting to .csv)

I am having a nightmare scenario trying to export excels data to a csv file for further processing. Hopefully someone here can help me, this is getting to be very frustrating.

The program I am exporting the data to needs to have numeric data in a format that uses dots for decimal points instead of commas as excel does. I also want to save the data as it is on the fields, without any additional formatting (such as numeric format adding ",00" or removing decimal points, or some other formats adding spaces to large numbers). Keep in mind that excel outputs the .csv file not according to the actual data, but the data modified through the format.

The solution in theory is extremely simple. Clear format on everything and then find and replace "," with ".". But, it's not that simple, even the unformatted cells for some reason convert to date fields, when swapping "," to ".". So now I have dates inside what is supposed to be numeric data. How can I stop this behavior and have truly unformatted pure data cells with no extra behavior going on?

You have commas for decimal points because of your Regional settings. I guess that is the same for dates.

I would suggest that you set up an empty sheet and, in a helper column, say column A, concatenate the data from all the original columns. Use TEXT, etc., to format the dates and so on. Then save the new sheet with the single text as your csv file.


Regards, TMS

You're welcome. Thanks for the rep.